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.

FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more (rare exceptions exist). Rather use the appropriate methods the XML data type provides. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 29, 2017 at 14:53

Serg · Accepted Answer · 2017-06-29 14:40:51Z

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

edited Jun 29, 2017 at 14:40

answered Jun 29, 2017 at 13:29

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jt2ouan Over a year ago

Not complex. Just lots of extra overhead in testing the change that would like to be avoided.

Jt2ouan Over a year ago

I guess this makes it a bit more challenging but the XML is longer and there are other cap elements but I only want the ones between the subject element not support You're code only grabs the first id attribute whereas there could be multiple. see editted question. Thank You!

Serg Over a year ago

Ok, need to specify parent by the path from cap up. see edit.

Jt2ouan Over a year ago

Perfecto. Thank you!!

Serg Over a year ago

@Shnugo, xml.nodes version was created before OP edited his question and it's abandoned as irrelevant in favor of OpenXML version.

|

Gottfried Lesigang · Accepted Answer · 2017-06-29 15:03:01Z

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;

Collectives™ on Stack Overflow

SQL Using OpenXML to retrieve multiple Elements

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related