在SQL中获取XML节点值 [英] Fetch XML Node Value in SQL

问题描述

首先这里是我正在处理的一小部分XML样本

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>

如何检索如果节点类型='文件'，则subvalue1和subvalue2的信息？



结果应显示如下

支付	模式
105	firstpayment

< TD> secondpayment

120

< TD> 180

firstpayment

<表> 260 firstpayment



我可以通过使用以下方法将数据加载到表中来检索数据。

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.

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)

但我只想要那些节点类型=文件的子节点的数据。截至目前它给了我所有子节点的价值（所​​以我得到6个结果而不是4个）。



任何人的帮助都将不胜感激。

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.

推荐答案

我会在将它传递给数据库之前对其进行过滤，但 exists（） [ ^ ]方法会做你想要的。

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

好的，测试它太热了，但是你试过了吗？



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

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

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

嗨。试试下面的代码块。

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

这篇关于在SQL中获取XML节点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！