Post reply

Shredding XML file into table using XQUERY

  • April 17, 2009 at 4:07 am

    #136788

    Hi guys,

    I am trying to shred this XML file and place its contents in this table.

    Here is the Sample XML

    1990

    Symbian

    China

    28723.00

    The following is the Xquery I am using to query the XML file, however I am not getting any results.. I get NULLs instead of the actual data. Can anyone shed any light on this... maybe I am missing something here?

    DECLARE @xmlvar xml

    SELECT @xmlvar = BulkColumn

    FROM OPENROWSET (BULK '\\myshare\TESTFILE.xml', SINGLE_BLOB) T

    SELECT

    x.location.value('yearmanufcature[1]', 'nvarchar(100)') AS yearmanuf,

    x.location.value('model[1]', 'nvarchar(100)') AS model,

    x.location.value('manufcountry[1]', 'nvarchar(100)') AS countrymanuf

    FROM @xmlvar.nodes('

    declare namespace s="http://data.com.at";

    /s:dataroot/s:vessels/s:vessel/s:vessel_info') AS x ( location )

    Thanks alot!

  • April 17, 2009 at 4:20 am

    #979523

    XML is case sensitive

    SELECT

    x.location.value('declare namespace s="http://data.com.at"; s:YEARMANUFCATURE[1]', 'nvarchar(100)') AS yearmanuf,

    x.location.value('declare namespace s="http://data.com.at"; s:MODEL[1]', 'nvarchar(100)') AS model,

    x.location.value('declare namespace s="http://data.com.at"; s:MANUFCOUNTRY[1]', 'nvarchar(100)') AS countrymanuf

    FROM @xmlvar.nodes('

    declare namespace s="http://data.com.at";

    /s:DATAROOT/s:VESSELS/s:VESSEL/s:VESSEL_INFO') AS x ( location )

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • April 17, 2009 at 5:55 am

    #979561

    Thanks a lot Mark! That really ws helpful.

    Can you also include any error handling and filtering (like where clause) within the xquery?

    Thanks again 🙂

  • April 17, 2009 at 6:41 am

    #979590

    You can use "contains" and "exist" for filtering. Not sure what you mean by error handling in this context.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply