Need help getting XML data out of SQL table

  • kevin.young 1284

    SSC Rookie

    Points: 38

    I have a SQL table with one data type=XML that I was able to successfully import the following XML file. I am now trying to query this field and return certain data. I've done this successfully before but something about this data is not working for me. From this data I wish to return <del_Nr>, <del_third_Nr>, <Product_Code> and <Product_Quantity> If I run the following it works as expected and returns:

    PONbr NCubeID InvtID QtyOrd

    ---------- ---------- ------------------------------ -----------

    1061 115 7439.10 1000

    (1 row(s) affected)

    declare @XML xml =

    '<message>

    <head />

    <body>

    <shipmentorder>

    <orderheader>

    <data>

    <purchase_Order> 1063</purchase_Order>

    <del_Nr> 1061</del_Nr>

    <del_Mode_Code>T</del_Mode_Code>

    <del_Date>23/01/2015</del_Date>

    <del_Third_Nr> 115</del_Third_Nr>

    <del_Is_Company>True</del_Is_Company>

    <del_Company_Name>CName</del_Company_Name>

    <del_Name>Name1</del_Name>

    <del_First_Name />

    <del_Extra_Name />

    <del_Contact />

    <del_Line_1>Centro Comercial OEIRAS PARQUE</del_Line_1>

    <del_Line_2>Av. Ant. Bernardo Cabral de Macedo</del_Line_2>

    <del_Post_Code>2770-219</del_Post_Code>

    <del_City>Paço de Arcos</del_City>

    <del_Area_Code>LI</del_Area_Code>

    <del_Country>PT</del_Country>

    <del_Phone> 555065595</del_Phone>

    <Line>

    <Product_Code>7439.10</Product_Code>

    <Product_Description>Roma</Product_Description>

    <Product_Quantity> 1000</Product_Quantity>

    </Line>

    </data>

    </orderheader>

    </shipmentorder>

    </body>

    </message>';

    SELECT

    o.value('(del_Nr/text())[1]', 'VARCHAR(10)') as PONbr,

    o.value('(del_Third_Nr/text())[1]', 'VARCHAR(10)') AS NCubeID,

    i.value('(Product_Code/text())[1]', 'VARCHAR(30)') AS InvtID,

    i.value('Product_Quantity[1]', 'INT') AS QtyOrd

    FROM @XML.nodes('/message/body/shipmentorder/orderheader/data') a(o)

    CROSS APPLY o.nodes('/message/body/shipmentorder/orderheader/data/Line') b(i)

    However if instead I try to query the table with the XML data using similar SQL it returns no data. xml_data is the data column with the XML data. I've done this same thing with very similar data and been successful. Not sure what it doesn't like this time.

    SELECT

    o.value('(del_Nr/text())[1]', 'VARCHAR(10)') as PONbr,

    o.value('(del_Third_Nr/text())[1]', 'VARCHAR(10)') AS NCubeID,

    i.value('(Product_Code/text())[1]', 'VARCHAR(30)') AS InvtID,

    i.value('Product_Quantity[1]', 'INT') AS QtyOrd

    FROM xNESP_XmlImportPOData CROSS APPLY

    xml_data.nodes('/message/body/shipmentorder/orderheader/data') a(o)

    CROSS APPLY o.nodes('/message/body/shipmentorder/orderheader/data/Line') b(i)

    Any ideas?

  • Mark Cowne

    One Orange Chip

    Points: 26752

    You've changed

    '/message/body'

    to

    '/body'

    in the second query

    ____________________________________________________

    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
  • kevin.young 1284

    SSC Rookie

    Points: 38

    Thanks but yes I had just caught that after posting. That was from a previous unsuccessful attempt at something. I updated my post minutes ago. It is still not working as expected even with that change.

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    I've just run your query, using a temp table of the same name + example xml, and it works fine.

    CREATE TABLE #xNESP_XmlImportPOData (xml_data XML)

    INSERT INTO #xNESP_XmlImportPOData (xml_data)

    VALUES ('<message>

    <head />

    <body>

    <shipmentorder>

    <orderheader>

    <data>

    <purchase_Order> 1063</purchase_Order>

    <del_Nr> 1061</del_Nr>

    <del_Mode_Code>T</del_Mode_Code>

    <del_Date>23/01/2015</del_Date>

    <del_Third_Nr> 115</del_Third_Nr>

    <del_Is_Company>True</del_Is_Company>

    <del_Company_Name>CName</del_Company_Name>

    <del_Name>Name1</del_Name>

    <del_First_Name />

    <del_Extra_Name />

    <del_Contact />

    <del_Line_1>Centro Comercial OEIRAS PARQUE</del_Line_1>

    <del_Line_2>Av. Ant. Bernardo Cabral de Macedo</del_Line_2>

    <del_Post_Code>2770-219</del_Post_Code>

    <del_City>Paço de Arcos</del_City>

    <del_Area_Code>LI</del_Area_Code>

    <del_Country>PT</del_Country>

    <del_Phone> 555065595</del_Phone>

    <Line>

    <Product_Code>7439.10</Product_Code>

    <Product_Description>Roma</Product_Description>

    <Product_Quantity> 1000</Product_Quantity>

    </Line>

    </data>

    </orderheader>

    </shipmentorder>

    </body>

    </message>')

    SELECT *

    FROM #xNESP_XmlImportPOData

    SELECT

    o.value('(del_Nr/text())[1]', 'VARCHAR(10)') as PONbr,

    o.value('(del_Third_Nr/text())[1]', 'VARCHAR(10)') AS NCubeID,

    i.value('(Product_Code/text())[1]', 'VARCHAR(30)') AS InvtID,

    i.value('Product_Quantity[1]', 'INT') AS QtyOrd

    FROM #xNESP_XmlImportPOData CROSS APPLY

    xml_data.nodes('/message/body/shipmentorder/orderheader/data') a(o)

    CROSS APPLY o.nodes('/message/body/shipmentorder/orderheader/data/Line') b(i)

    PONbr NCubeID InvtID QtyOrd

    1061 115 7439.10 1000

    So at this point I'd sanity check the base table to see that the data is there in the format that you expect.

  • kevin.young 1284

    SSC Rookie

    Points: 38

    I guess sometimes the simplest sanity checks are the best. I re-imported the XML file and now it is working as expected. What's odd is that I swear I did that earlier too and was still having a problem so had ruled that out as the cause. But when you proved my logic was correct by creating the temp table and reproducing exactly what I'm trying to do then I saw that the data was still the problem. I should have thought to do that myself. Not sure what I did wrong on the re-import previously but now it's good.

    Thanks!!

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    No problem.. thanks for the feedback 🙂

  • Alan Burstein

    SSC Guru

    Points: 61079

    You can lose one cross apply and further simplify the query like this:

    SELECT

    o.value('(del_Nr/text())[1]', 'VARCHAR(10)') as PONbr,

    o.value('(del_Third_Nr/text())[1]', 'VARCHAR(10)') AS NCubeID,

    o.value('(Line/Product_Code/text())[1]', 'VARCHAR(30)') AS InvtID,

    o.value('(Line/Product_Quantity/text())[1]', 'INT') AS QtyOrd

    FROM #xNESP_XmlImportPOData

    CROSS APPLY xml_data.nodes('/message/body/shipmentorder/orderheader/data') a(o);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • kevin.young 1284

    SSC Rookie

    Points: 38

    Thanks! I think I tried a variation of this too which didn't work. But it didn't work at the time because the data was flawed in general. I will revisit this.

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

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