SQL Using OpenXML to retrieve multiple Elements

Question

I'm querying using OpenXML to retrieve the cap elements between the subject elements in XML I don't want the cap between the support elemements. The query works great to retrieve one value but fails when there are multiple element nodes.

      <First>
         <Test id="83847">
            <subject>
               <cap>15</cap>
               <cap>25</cap>
               <cap>100</cap>
            </subject>
            <support>
               <cap>9</cap>
            </support>
          </Test>
        <Test id="83848">
            <subject>
               <cap>150</cap>
               <cap>2</cap>
               <cap>10</cap>
            </subject>
             <support>
               <cap>9</cap>
            </support>
          </Test>
      </First>

CREATE Table #XmlTemp(XmlField Xml);
Set Nocount On;
Insert Into #XmlTemp(XmlField)
Select '<First>
         <Test id="83847">
             <subject>
                <cap>15</cap>
                <cap>25</cap>
                <cap>100</cap>
             </subject>
             <support>
                <cap>9</cap>
             </support>
          </Test>
        <Test id="83848">
            <subject>
               <cap>150</cap>
               <cap>2</cap>
               <cap>10</cap>
            </subject>
             <support>
               <cap>9</cap>
            </support>
          </Test>
      </First>'As XmlField;

Declare @xmlData Xml;
Select @xmlData = XmlField From #XmlTemp;

Declare @document int;
Exec sp_xml_preparedocument @document Output, @xmlData, NULL;


SELECT ID,Cap FROM(
SELECT ID,Cap FROM OpenXml(@document,'./First/Test', 0) With (ID varchar(max)'./@id', Cap Varchar(max) './subject/cap')) alias

drop table #xmltemp

It'd be fairly time consuming to change the query to use .nodes method more so because of the testing involved so I'd like it to stay as OpenXML if possible. I'd only like to retrieve out the ID and then the multiple cap element values.

Thank you for your time.

Answer 1

Your XML is double nested. You have 1:n of <Test> elements within <First> and again 1:n of <cap> elements within <subject>.

The proper way to query this is diving into the XML strictly forward:

CREATE Table #XmlTemp(XmlField Xml);
Set Nocount On;
Insert Into #XmlTemp(XmlField)
Select '<First>
         <Test id="83847">
             <subject>
                <cap>15</cap>
                <cap>25</cap>
                <cap>100</cap>
             </subject>
             <support>
                <cap>9</cap>
             </support>
          </Test>
        <Test id="83848">
            <subject>
               <cap>150</cap>
               <cap>2</cap>
               <cap>10</cap>
            </subject>
             <support>
               <cap>9</cap>
            </support>
          </Test>
      </First>'As XmlField;

--The query will use .nodes() to get all <Test> elements and again .nodes() to get the related <cap> elements:

      SELECT t.value('@id', 'int') id
            ,c.value('text()[1]', 'int') cap
    from #XmlTemp AS tbl
    CROSS APPLY tbl.XmlField.nodes('/First/Test') AS A(t)
    CROSS APPLY A.t.nodes('subject/cap') AS B(c);
GO
DROP TABLE #XmlTemp;

Answer 2

I can't see why the query using .nodes is complex. Just

SELECT t.n.value('(/First/Test/@id)[1]', 'int') id
   , t.n.value('(.)[1]', 'int') cap
from @xmlData.nodes('./First/Test/subject/cap') t(n);

And OpenXML version

SELECT ID,Cap FROM(
    SELECT ID,Cap 
    FROM OpenXml(@document,'./First/Test/subject/cap', 0) 
       With (ID varchar(max) '/First/Test/@id'
          , Cap Varchar(max) '.')) alias

Version for the edited question

SELECT ID,Cap FROM(
    SELECT ID,Cap 
    FROM OpenXml(@document,'/First/Test/subject/cap', 0) 
       With (ID varchar(max) '../../@id'
          , Cap Varchar(max) '.')) alias

It returns only subject/cap and @id of the proper parent:

    ID  Cap
1   83847   15
2   83847   25
3   83847   100
4   83848   150
5   83848   2
6   83848   10