+ Post New Thread
Results 1 to 4 of 4
Web Development Thread, Dealing with 'missing' XML on import to SQL in Coding and Web Development; I'm not far off losing the will to live with this one. I'm trying to import an XML file in ...
  1. #1

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,433
    Thank Post
    1,432
    Thanked 1,161 Times in 794 Posts
    Rep Power
    705

    Dealing with 'missing' XML on import to SQL

    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:

    Code:
    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?
    Last edited by LosOjos; 18th October 2013 at 11:08 AM.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,433
    Thank Post
    1,432
    Thanked 1,161 Times in 794 Posts
    Rep Power
    705
    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...

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,433
    Thank Post
    1,432
    Thanked 1,161 Times in 794 Posts
    Rep Power
    705
    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!

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,433
    Thank Post
    1,432
    Thanked 1,161 Times in 794 Posts
    Rep Power
    705
    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]

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 4
    Last Post: 25th June 2009, 02:39 PM
  2. Seeking advice on way to go with upgrade
    By speckytecky in forum Wireless Networks
    Replies: 2
    Last Post: 15th June 2009, 06:33 PM
  3. A little issue to deal with
    By bottletop in forum Windows
    Replies: 6
    Last Post: 5th June 2009, 09:07 AM
  4. Best way to deal with Active Directory
    By apeo in forum Scripts
    Replies: 2
    Last Post: 22nd April 2009, 08:36 AM
  5. How to deal with ebay scammers
    By Oops_my_bad in forum General Chat
    Replies: 26
    Last Post: 16th October 2007, 07:47 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •