XML parsing via SQL Server

Question

I am trying to parser below XML with the mentioned code. But results are blank.

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn AS XML 
FROM OPENROWSET(BULK 'C:\GET_QUOTA_DATA_resTEST.xml', SINGLE_BLOB) x

SELECT 
BucketBucket_id = bucket.value('(bucket_id)[1]', 'varchar(255)'),
BucketName = bucket.value('(name)[1]', 'varchar(255)')
FROM
@XmlFile.nodes('/bucket') AS XTbl1(bucket)

XML is as below

    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:toa:capacity">
       <SOAP-ENV:Body>
          <ns1:get_quota_data_response>
            <bucket>
             <bucket_id>FT_10503_DP1019</bucket_id>
             <name>ABC Co</name>
                <day>
                    <date>2015-08-21</date>
                </day>
           </bucket>
      </ns1:get_quota_data_response>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Is that all XML? It is not correctly formed. Try SELECT @XmlFile/PRINT @XmlFile. Describe also desired result. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 23, 2015 at 1:53
@lad2025 It's fine above I haven't put the whole XML may be that's why. Updated now. — Shabar
– Shabar, Commented Aug 23, 2015 at 2:02

marc_s · Accepted Answer · 2015-08-23 07:24:02Z

1

Try this:

 SELECT 
     BucketBucket_id = bucket.value('(bucket_id)[1]', 'varchar(255)'),
     BucketName = bucket.value('(name)[1]', 'varchar(255)')
 FROM 
     @XmlFile.nodes('//bucket') AS XTbl1(bucket)

You used /bucket which is direct path from root. That's why it didn't work.

/ will define an absolute path to node "a" relative to the root
// will define a path to node "a" anywhere within the XML document

edited Aug 23, 2015 at 7:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 23, 2015 at 2:00

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Shabar Over a year ago

Excellent // made the difference.

Collectives™ on Stack Overflow

XML parsing via SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related