Extract a value from SQL XML field

Question

Using MS SQL Server, I've got some data in an XML field (called XML), which is structured like this:

<Transaction01>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>

I'm trying to create a SQL query to fetch another column called SubmittedDate, along with the PO_NBR from this XML field. Being new to XPath, I've read numerous examples and tried both query and value, but I've not been successful yet. For example:

SELECT SubmittedDate, 
       XML.query('data(/POHeader/PO_NBR)') as PO_NBR
  FROM SubmitXML

This just gives me a empty column. After getting a working test from Quassnoi, I worked from his XML to mine, and discovered the problem is the xmlns and xmlns:i attributes in the root node:

<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

So how do I get around that?

Answer 1

SELECT  SubmittedDate, 
        XML.query('data(/Transaction01/POHeader/PO_NBR)') as PO_NBR
FROM    SubmitXML

You original XPath, /POHeader/PO_NBR, assumed that POHeader is the root node (which is not).

A sample query to check:

DECLARE @myxml XML
SET @myxml = '
<Transaction01>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>'

SELECT  @myxml.query('data(/Transaction01/POHeader/PO_NBR)')

If Transaction01 is not always the root node (which is not a good thing), use this:

SELECT  SubmittedDate, 
        XML.query('data(/*/POHeader/PO_NBR)') as PO_NBR
FROM    SubmitXML

Generally, XML schema assumes that the tag names are fixed and the variable parts go to the data of the nodes and the attributes rather than into their names, like this:

<Transaction id='01'>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction>

Update:

You should declare the namespaces using WITH XMLNAMESPACES:

DECLARE @myxml XML
SET @myxml = '
<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>'

;
WITH    XMLNAMESPACES
        (
        'http://services.iesltd.com/' AS m
        )
SELECT  @myxml.query
        (
        'data(/*/m:POHeader/m:PO_NBR)'
        )

Update 2:

To sort:

;
WITH    XMLNAMESPACES
        (
        'http://services.iesltd.com/' AS m
        )
SELECT  SubmittedDate, 
        XML.value('(/*/m:POHeader/m:PO_NBR)[1]', 'NVARCHAR(200)') AS po_nbr
FROM    SubmitXML
ORDER BY
        po_nbr

Answer 2

If you want to avoid hardcoding Transaction01, you can try this:

SELECT  SubmittedDate,          
        XML.query('data(//POHeader/PO_NBR)') as PO_NBR 
  FROM  SubmitXML 

E.G:

CREATE TABLE #TEMP (XMLTEXT XML)

INSERT INTO #TEMP 
SELECT '<Transaction01>   <TransactionSetPurpose>Insert</TransactionSetPurpose>   <POHeader>     <PO_NBR>LG40016181</PO_NBR>   </POHeader> </Transaction01> '

SELECT XMLTEXT.query('data(//POHeader/PO_NBR)') as PO_NBR   
 FROM #TEMP