Need help getting XML data out of SQL table

Question

Post reply

Need help getting XML data out of SQL table

kevin.young 1284

SSC Rookie

Points: 38
More actions
January 28, 2015 at 7:22 am

#296948

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?

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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 More actions · Answer 2

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: 6639 More actions · Answer 3

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)

PONbrNCubeIDInvtIDQtyOrd

1061 1157439.101000

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 More actions · Answer 4

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: 6639 More actions · Answer 5

Arthur Olcot

SSCertifiable

Points: 6639

January 28, 2015 at 8:08 am

#1773699

No problem.. thanks for the feedback 🙂

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

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 More actions · Answer 7

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.