Fetch XML Node Value in SQL

Question

0.00/5 (No votes)

See more:

First off here is a small sample of XML that i am working on

XML

<RootNode>
  <node type="FILE">
    <subNode>
      <subValue1>105</subValue1>
      <subValue2>firstpayment</subValue2>
    </subNode>
    <subNode>
      <subValue1>120</subValue1>
      <subValue2>secondpayment</subValue2>
    </subNode>
  </node>
  <node type="DIR">
    <subNode>
      <subValue1>300</subValue1>
      <subValue2>firstpayment</subValue2>
    </subNode>
    <subNode>
      <subValue1>350</subValue1>
      <subValue2>secondpayment</subValue2>
    </subNode>
  </node>
  <node type="FILE">
    <subNode>
      <subValue1>180</subValue1>
      <subValue2>firstpayment</subValue2>
    </subNode>
    <subNode>
      <subValue1>260</subValue1>
      <subValue2>secondpayment</subValue2>
    </subNode>
  </node>
</RootNode>

How to retrieve the information of subvalue1 and subvalue2 if node type='File' ?

Result should show as below

Pay	Mode
105	firstpayment

120	secondpayment

180	firstpayment

260	firstpayment

I am able to retrieve the data by loading the data to a table by using the following method.

SQL

insert into @Table values (@xml) --data above

SELECT 
DATA.query('(./subValue1)').value('.','INT') as PAY,
DATA.query('(./subValue2)').value('.','VARCHAR(MAX)') as MODE
FROM @Table TF 
CROSS APPLY @xml.nodes('/RootNode/node/subNode') AS HF(DATA)

But I want data of only those subnode where node type="File". as of now it is giving me value of all subnode ( So i get 6 results instead of only 4).

Any help from anyone would be greatly appreciated.

Posted 17-Jun-13 12:49pm

Vamshi Krishna Naidu

Updated 17-Jun-13 13:46pm

PIEBALDconsult

v4

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2013-06-17T13:21:00

Solution 1

I would filter it before passing it to the DB, but the exist()[^] method will do what you want.

Posted 17-Jun-13 13:21pm

Christian Graus

Comments

PIEBALDconsult 17-Jun-13 19:47pm

I'm thinking value(), rather than exist().

Christian Graus 17-Jun-13 19:48pm

But he's getting the values, he wants to only select them if particular values exist ?

PIEBALDconsult 17-Jun-13 19:51pm

Oh, I see. Then I'd improve the XPath to filter that. Now to test...

Christian Graus 17-Jun-13 19:52pm

Ah - good point. I said to filter before it goes in, I didn't spot that the 'cross apply' actually allows full xpath, I assume that's what you're saying ?

Vamshi Krishna Naidu 18-Jun-13 14:13pm

Ok. Solution 2 did the job but it was not giving me more options to play through. So i tried using exist() and few other data type methods and it fulfilled my requirement.

+5 for this solution.

PIEBALDconsult · Accepted Answer · 2013-06-17T13:56:00

Solution 2

OK, it's too hot to test it, but have you tried:

'/RootNode/node[@type="File"]/subNode'

Posted 17-Jun-13 13:56pm

PIEBALDconsult

Updated 18-Jun-13 13:55pm

v3

Comments

damodara naidu betha 18-Jun-13 7:59am

5+

Vamshi Krishna Naidu 18-Jun-13 14:15pm

Worked like a charm.

PIEBALDconsult 18-Jun-13 19:55pm

Glad to hear it.

damodara naidu betha · Accepted Answer · 2013-06-18T02:06:00

Hi. Try this folowing code block.

SQL

-- declare xml variable --
DECLARE @XML xml = '<rootnode>
  <node type="FILE">
    <subnode>
      <subvalue1>105</subvalue1>
      <subvalue2>firstpayment</subvalue2>
    </subnode>
    <subnode>
      <subvalue1>120</subvalue1>
      <subvalue2>secondpayment</subvalue2>
    </subnode>
  </node>
  <node type="DIR">
    <subnode>
      <subvalue1>300</subvalue1>
      <subvalue2>firstpayment</subvalue2>
    </subnode>
    <subnode>
      <subvalue1>350</subvalue1>
      <subvalue2>secondpayment</subvalue2>
    </subnode>
  </node>
  <node type="FILE">
    <subnode>
      <subvalue1>180</subvalue1>
      <subvalue2>firstpayment</subvalue2>
    </subnode>
    <subnode>
      <subvalue1>260</subvalue1>
      <subvalue2>secondpayment</subvalue2>
    </subnode>
  </node>
</rootnode>'
 
 -- query block --
SELECT a.b.value('subValue1[1]','varchar(10)') as Pay,
a.b.value('subValue2[1]','varchar(20)') As Mode
FROM @XML.nodes('RootNode/node[@type="FILE"]/subNode') a(b)

Thank you