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:
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.
INSERT INTO SIMRA.dbo.Gradesets (ID, GradesetID, GradesetName, Grade, Points)
SELECT X.gradeset.query('multiple_id').value('.', 'nvarchar(255)'),
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);
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?