Dealing with 'missing' XML on import to SQL

[LosOjos]

I'm not far off losing the will to live with this one.

 

I'm trying to import an XML file in to an SQL 2008r2 Express server. It works fine if all nodes exist, but if any nodes are missing it fails. This is a big problem as I don't have control of the produced XML (it's coming from SIMS), plus it's perfectly legitimate XML behaviour so I'm surprised SQL doesn't know how to handle it automatically...

 

Here is the SQL query I'm using:

 

INSERT INTO SIMRA.dbo.Gradesets (ID, GradesetID, GradesetName, Grade, Points) 
SELECT 	X.gradeset.query('multiple_id').value('.', 'nvarchar(255)'),
X.gradeset.query('ID').value('.', 'nvarchar(40)'),  
X.gradeset.query('Gradeset_x0020_name').value('.', 'nvarchar(40)'),
X.gradeset.query('Grade').value('.', 'nvarchar(40)'), 
X.gradeset.query('Value').value('.', 'decimal(6,2)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'C:\SIMRA\gradesets.xml', SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('SuperStarReport/Record') AS X(gradeset);

 

I'm running this from sqlcmd as it needs to be scheduled to run overnight; as you probably know, sqlcmd's errors are almost useless in identifying problems! I get an 'Error converting nvchar to numeric' error, I'm almost certain it's caused by the fact that sometimes, the 'Points' node doesn't exist in a given parent node. I'm using this same query on another SIMS produced XML which is more strict (i.e. all nodes always exist) and that works fine.

 

My Google-fu is seriously failing me on me this, everything I'm finding refers to depositing the XML data directly in to a database field, not mapping the nodes to columns as I want to. Any ideas?

Edited October 18, 2013 by LosOjos

[LosOjos]

I should add that I'm certainly no SQL guru so this may well be an awful way to import XML, I'm open to suggestions.

 

Previously I've used VBS to do checking and conversion but it was extremely slow and long winded, I had hoped I'd be able to do it with pure SQL queries...

[LosOjos]

It works fine if I change the type of Points to nvarchar (in both the DB and the query) but I'd really have liked to have stored it as a decimal value, as that's exactly what it is!

[LosOjos]

There seems to be some issue with the decimal data type in SQL, nothing I seem to be able to do to update values from XML. I'm going to try some manual decimal updates on a temp table at some point, just out of curiosity, but for now I've given up on the decimal type. nvarchar and run time conversion it is!

 

[bTW - I like to update these threads because they prompt me to go back and look at problems when I read them, I'm not in the habit of talking to myself, honest]