Click here to Skip to main content
14,423,437 members
Rate this:
Please Sign up or sign in to vote.
See more:
First off here is a small sample of XML that i am working on

<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

PayMode
105firstpayment
120secondpayment
180firstpayment
260firstpayment


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

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
Updated 17-Jun-13 14:46pm
v4
Rate this:
Please Sign up or sign in to vote.

Solution 1

I would filter it before passing it to the DB, but the exist()[^] method will do what you want.
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

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

'/RootNode/node[@type="File"]/subNode'
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Hi. Try this folowing code block.
-- 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
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100