Get values from XML using XQuery

Question

I am new in T-SQL and XQuery.

I have XML column in DB with structure like this

     <GPC xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="...">
  <GP>
    <N>Amount1</N>
    <V i:type="X_AMT">
      <AMT>100001</AMT>
      <X_CODE>Dollar</X_CODE>
    </V>
  </GP>
  <GP>
    <N>Amount2</N>
    <V i:type="X_AMT">
      <AMT>0</AMT>
      <X_CODE />
    </V>
  </GP>
  <GP>
    <N>Amount3</N>
    <V i:type="X_AMT">
      <AMT>100001</AMT>
      <X_CODE>Dollar</X_CODE>
    </V>
  </GP>
  <GP>
    <N>Amount4</N>
    <V i:type="X_AMT">
      <AMT>0</AMT>
      <X_CODE />
    </V>
  </GP>
  <GP>
    <N>Amount5</N>
    <V i:type="X_AMT">
      <AMT>100001</AMT>
      <X_CODE>Dollar</X_CODE>
    </V>
  </GP>
  <GP>
    **<N>NeededAmount</N>**
    <V i:type="Y">
      <DETAILS>
        <REFERENCE>
          <N>**ReferenceName1**</N>
          <OId>111111</OId>
        </REFERENCE>
      </DETAILS>
      <DETAILS>
        <REFERENCE>
          <N>**ReferenceName2**</N>
          <OId>22222</OId>
        </REFERENCE>
      </DETAILS>
    </V>
  </GP>

  ...

  </GPC>

This is query in SQL Server that i am using. The Query returns only one name - Name1. But there is two names, and there can be a 100 names, and i want to get that names.

 SELECT v.Content.query(N'declare default element namespace "..."; 
    for $i in (GPC/GP) where ($i/N[1] eq "NeededAmount") return ($i)').value('declare default element namespace "..."; 
    (GP/V/DETAILS/REFERENCE/N)[1]', 'nvarchar(max)') AS NeededName
    FROM DB1.protected.WorkItem as v
    where v.Id = 1111

My qustion is: How can i modify this to get all names in document? Can someone help me?

Thanks in advance.

Answer 1

Try something like this:

;WITH XMLNAMESPACES(DEFAULT '...')
SELECT 
    Amount = XTbl.GP.value('(N)[1]', 'nvarchar(100)'),
    NeededName = XTbl2.DetRef.value('(N)[1]', 'nvarchar(200)')
FROM 
    Table1 AS t
CROSS APPLY
    t.XmlContent.nodes('/GPC/GP') AS XTbl(GP)
CROSS APPLY
    XTbl.GP.nodes('V/DETAILS/REFERENCE') AS XTbl2(DetRef)
WHERE 
    wi.Id = 1111

Answer 2

You need to "shred" the xml into rows using the nodes() Transact-SQL XML datatype method. The nodes() method is used in the FROM clause and produces 1 row for each matching node in the xml document.

here is a example using xml stored in a variable:

 DECLARE @x XML
 SET @x = N'     <GPC xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" >
   <GP>
   <GP>
      <N>Amount5</N>
      <V i:type="X_AMT">
        <AMT>100001</AMT>
        <X_CODE>Dollar</X_CODE>
      </V>
   </GP>
   <GP>
      **<N>NeededAmount</N>**
      <V i:type="Y">
        <DETAILS>
           <REFERENCE>
             <N>**ReferenceName1**</N>
             <OId>111111</OId>
           </REFERENCE>
        </DETAILS>
        <DETAILS>
           <REFERENCE>
             <N>**ReferenceName2**</N>
             <OId>22222</OId>
           </REFERENCE>
        </DETAILS>
      </V>
   </GP>
   </GPC>
 '


 SELECT x.n.query('.') FROM @x.nodes('/GPC/GP/V/DETAILS/REFERENCE/N') AS x (n)
 /* returns 
 <N>**ReferenceName1**</N>
 <N>**ReferenceName2**</N> */

You can also use CROSS APPLY to shred an xml column in a table

 CREATE TABLE #t (id INT, doc XML)
 go
  DECLARE @x XML
 SET @x = N'     <GPC xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" >
   <GP>
      <N>Amount5</N>
      <V i:type="X_AMT">
        <AMT>100001</AMT>
        <X_CODE>Dollar</X_CODE>
      </V>
   </GP>
   <GP>
      **<N>NeededAmount</N>**
      <V i:type="Y">
        <DETAILS>
           <REFERENCE>
             <N>**ReferenceName1**</N>
             <OId>111111</OId>
           </REFERENCE>
        </DETAILS>
        <DETAILS>
           <REFERENCE>
             <N>**ReferenceName2**</N>
             <OId>22222</OId>
           </REFERENCE>
        </DETAILS>
      </V>
   </GP>
   </GPC>
 '
 INSERT INTO #t (id, doc) VALUES (1, @x)
 INSERT INTO #t (id, doc) VALUES (2, @x)


 SELECT t.id, x.n.query('.') RefName FROM #t t CROSS APPLY doc.nodes('/GPC/GP/V/DETAILS/REFERENCE/N') AS x (n)
 --returns 4 rows, 2 for each document
 /*
 id            RefName
 1     <N>**ReferenceName1**</N>
 1     <N>**ReferenceName2**</N>
 2     <N>**ReferenceName1**</N>
 2     <N>**ReferenceName2**</N> */