SQL Server XML 提取节点

发布于 2024-09-24 23:11:50 字数 783 浏览 5 评论 0原文

我正在使用 SQL Server 2005 来提取 XML 数据。我希望能得到一些帮助来提取以下 XML 片段？

<ELEMENT_A>
    <CHILD_A _attribA="ABC">
       <CHILD_B Type="1">
          <CHILD_C>
             <CHILD_D Type="1" Date="2010-08-31">
                <CHILD_E _attribB="M0">
                   <CHILD_F>-0.32295</CHILD_F>
                </CHILD_E>
            </CHILD_D>
         </CHILD_C>
     </CHILD_B>
   </CHILD_A>
</ELEMENT_A>

如何获取以下表格格式的数据？元素_A | _attribA |孩子_A |儿童_B |儿童_C |儿童_D |儿童_E | CHILD_F
注意：我想使用按节点选择，例如以下内容获取第一个属性的值：
选择 v.value('@_attribA[1]','nvarchar(4)') 来自 @doc.nodes('/ELEMENT_A/CHILD_A') x(v)

非常感谢，谢谢。

原文

I am using SQL Server 2005 to Extract XML data. I would appreciate some help extracting the following XML snippet?

<ELEMENT_A>
    <CHILD_A _attribA="ABC">
       <CHILD_B Type="1">
          <CHILD_C>
             <CHILD_D Type="1" Date="2010-08-31">
                <CHILD_E _attribB="M0">
                   <CHILD_F>-0.32295</CHILD_F>
                </CHILD_E>
            </CHILD_D>
         </CHILD_C>
     </CHILD_B>
   </CHILD_A>
</ELEMENT_A>

How do I get the data in the following tabular format?
ELEMENT_A | _attribA | CHILD_A | CHILD_B | CHILD_C | CHILD_D | CHILD_E | CHILD_F
Note: I'd like to use select by nodes e.g. the following gets me the value of the first Attribute:
select
v.value('@_attribA[1]','nvarchar(4)')
from @doc.nodes('/ELEMENT_A/CHILD_A') x(v)

Very much appreciated, Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戈亓 2024-10-01 23:11:50

希望这对你来说还不算太晚。我今天才看到你的问题

select 
v.value('../../../../../@_attribA','nvarchar(4)')  as _attribA
,v.value('../../@Date','nvarchar(12)') as [Date]
,v.value('../@_attribB','nvarchar(2)') as _attribB
,v.value('.','float') as CHILD_F
,v.query('../../../../..') as CHILD_A

from @doc.nodes('/ELEMENT_A/CHILD_A/CHILD_B/CHILD_C/CHILD_D/CHILD_E/CHILD_F') x(v)

Hope this is not too late for you. I saw your question just today

select 
v.value('../../../../../@_attribA','nvarchar(4)')  as _attribA
,v.value('../../@Date','nvarchar(12)') as [Date]
,v.value('../@_attribB','nvarchar(2)') as _attribB
,v.value('.','float') as CHILD_F
,v.query('../../../../..') as CHILD_A

from @doc.nodes('/ELEMENT_A/CHILD_A/CHILD_B/CHILD_C/CHILD_D/CHILD_E/CHILD_F') x(v)

回复收藏 0 原文

~没有更多了~