SQL XML Nodes Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL XML Nodes Question

Author

Topic

kentap
Starting Member

2 Posts

Posted - 2010-10-07 : 12:27:40

Hi there, my existing stored procedures are using the OPENXML but I've read recently that I should be using the NODES functionality. I therefore decided to try and get this new functionality working but I've run into some problems. See xml below that is passed to the stored proc (looks messy but if you extract and paste into xml spy it is legible)

<CTRL_SEG>
<TRNNAM>INVENTORY RECONCILIATION</TRNNAM>
<TRNVER>0002</TRNVER>
<INVENTORY_RECONCILIATION_SEG>
<SITCOD>Stri</SITCOD>
<PRTNUM>002K16425</PRTNUM>
<ORGCOD>String</ORGCOD>
<REVLVL>String</REVLVL>
<LOTNUM>String</LOTNUM>
<INVSTS>FREE</INVSTS>
<HSTACC>String</HSTACC>
<UNTQTY>21</UNTQTY>
<PRT_CLIENT_ID>PSD</PRT_CLIENT_ID>
<STKUOM>St</STKUOM>
<CATCH_QTY>String</CATCH_QTY>
<CATCH_UNTTYP>String</CATCH_UNTTYP>
<WH_ID>String</WH_ID>
<TRNNUM>String</TRNNUM>
<TRNDTE>String</TRNDTE>
</INVENTORY_RECONCILIATION_SEG>
<INVENTORY_RECONCILIATION_SEG>
<SITCOD>Stri</SITCOD>
<PRTNUM>005E21090</PRTNUM>
<ORGCOD>String</ORGCOD>
<REVLVL>String</REVLVL>
<LOTNUM>String</LOTNUM>
<INVSTS>FREE</INVSTS>
<HSTACC>String</HSTACC>
<UNTQTY>20000</UNTQTY>
<PRT_CLIENT_ID>CTO</PRT_CLIENT_ID>
<STKUOM>St</STKUOM>
<CATCH_QTY>String</CATCH_QTY>
<CATCH_UNTTYP>String</CATCH_UNTTYP>
<WH_ID>String</WH_ID>
<TRNNUM>String</TRNNUM>
<TRNDTE>String</TRNDTE>
</INVENTORY_RECONCILIATION_SEG>
</CTRL_SEG>

the following code I am trying to use to extract the xml values, but it is not working.

SELECT
Node.value('.', 'VARCHAR(50)') AS SITCOD,
Node.value('.', 'VARCHAR(50)') AS PRTNUM,
Node.value('.', 'VARCHAR(50)') AS ORGCOD,
Node.value('.', 'VARCHAR(50)') AS REVLVL,
Node.value('.', 'VARCHAR(50)') AS LOTNUM,
Node.value('.', 'VARCHAR(50)') AS INVSTS,
Node.value('.', 'VARCHAR(50)') AS HSTACC,
Node.value('.', 'VARCHAR(50)') AS UNTQTY,
Node.value('.', 'VARCHAR(50)') AS PRT_CLIENT_ID,
Node.value('.', 'VARCHAR(50)') AS STKUOM,
Node.value('.', 'VARCHAR(50)') AS CATCH_QTY,
Node.value('.', 'VARCHAR(50)') AS CATCH_UNTTYP,
Node.value('.', 'VARCHAR(50)') AS WH_ID,
Node.value('.', 'VARCHAR(50)') AS TRNNUM,
Node.value('.', 'VARCHAR(50)') AS TRNDTE
FROM
@XMLString.nodes('CTRL_SEG/INVENTORY_RECONCILIATION_SEG') TempXML (Node);

Any ideas? I've seen many examples on the web but these examples are extracting data from attributes; I do not have any attributes. I need to be able to extract the elements value, i.e. <UNTQTY>20000</UNTQTY>
so 20000 will be the value that I need.

Thanks for reading...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-07 : 12:55:37

Try this:

SELECT Node.value('SITCOD[1]', 'VARCHAR(50)') AS SITCOD,
Node.value('PRTNUM[1]', 'VARCHAR(50)') AS PRTNUM,
Node.value('ORGCOD[1]', 'VARCHAR(50)') AS ORGCOD,
Node.value('REVLVL[1]', 'VARCHAR(50)') AS REVLVL,
Node.value('LOTNUM[1]', 'VARCHAR(50)') AS LOTNUM,
Node.value('INVSTS[1]', 'VARCHAR(50)') AS INVSTS,
Node.value('HSTACC[1]', 'VARCHAR(50)') AS HSTACC,
Node.value('UNTQTY[1]', 'VARCHAR(50)') AS UNTQTY,
Node.value('PRT_CLIENT_ID[1]', 'VARCHAR(50)') AS PRT_CLIENT_ID,
Node.value('STKUOM[1]', 'VARCHAR(50)') AS STKUOM,
Node.value('CATCH_QTY[1]', 'VARCHAR(50)') AS CATCH_QTY,
Node.value('CATCH_UNTTYP[1]', 'VARCHAR(50)') AS CATCH_UNTTYP,
Node.value('WH_ID[1]', 'VARCHAR(50)') AS WH_ID,
Node.value('TRNNUM[1]', 'VARCHAR(50)') AS TRNNUM,
Node.value('TRNDTE[1]', 'VARCHAR(50)') AS TRNDTE
FROM @XMLString.nodes('CTRL_SEG/INVENTORY_RECONCILIATION_SEG') TempXML (Node);

kentap
Starting Member

2 Posts

Posted - 2010-10-08 : 04:50:55

Hi robvolk, thanks for the prompt reply. Yes, your sql worked perfectly!! Can you explain to me what the [1] is all about? Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 08:05:38

It's explained better in Books Online, but it references the first element of that name in the node. If you use [2] it will reference the 2nd element, and so on. Even if you only have 1 element of each in the node you still need to specify the element number. Kinda annoying, but that sums up XML pretty well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources