SQL 查询 XML 属性 [英] SQL querying XML attributes
问题描述
我有一个 XML,我想用 SQL 查询它.
I have an XML that I'm trying to query with SQL.
<QueryB>
<investment name="InvestmentA">
<Account Type="DIVIDEND">
<glsum YTD="0.0000" />
<glsum Inception="111111.0000" />
<glsum QTD="0.0000" />
</Account>
</investment>
<investment name="InvestmentB">
<Account Type="DIVIDEND">
<glsum YTD="0.0000" />
<glsum Inception="222222.0000" />
<glsum QTD="0.0000" />
</Account>
</investment>
<investment name="InvestmentC">
<Account Type="CAP">
<glsum YTD="90.0000" />
<glsum Inception="333333.0800" />
<glsum QTD="90.0000" />
</Account>
</investment>
<investment name="InvestmentD">
<Account Type="CAP">
<glsum YTD="0.0000" />
<glsum Inception="555555.0000" />
<glsum QTD="0.0000" />
</Account>
<Account Type="DIVIDEND">
<glsum YTD="0.0000" />
<glsum Inception="444444.0000" />
<glsum QTD="0.0000" />
</Account>
</investment>
请注意,InvestmentD 同时具有股息和上限帐户类型.所以我尝试使用以下内容查询此数据:
Notice that InvestmentD has both a Dividend and Cap account type. So I tried to query this data with the following:
select rtrim(ltrim(t.c.value('@name', 'nvarchar(500)'))) as name,
rtrim(ltrim(t.c.value('(Account/@Type)[1]', 'nvarchar(500)'))) as Type,
rtrim(ltrim(t.c.value('(Account/glsum/@YTD)[1]', 'nvarchar(500)'))) as YTD,
rtrim(ltrim(t.c.value('(Account/glsum/@Inception)[1]', 'nvarchar(500)'))) as inception,
rtrim(ltrim(t.c.value('(Account/glsum/@QTD)[1]', 'nvarchar(500)'))) as QTD
from @x.nodes('/QueryB/investment')t(c)
其中@x 是 XML.这并不奇怪并没有从 InvestmentD 中提取两个节点.我不知道如何解析所有节点.正确方向的指针将不胜感激.谢谢.
where @x is the XML. This unsurprising does not pick up both nodes from InvestmentD. I can't figure out how to parse all the nodes. A pointer in the right direction would be appreciated. Thanks.
推荐答案
添加一个 cross apply tcnodes('Account') as a(c)
并从 ac 中获取属性值
没有在 xPath 表达式中指定 Acount
.
Add a cross apply t.c.nodes('Account') as a(c)
and fetch the attribute values from a.c
without specifying Acount
in the xPath expression.
名称仍应使用 t.c
.
这篇关于SQL 查询 XML 属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!