从SQL Server导入的XML中获取所有子级
从SQL Server导入的XML中获取所有子级
提问于 2017-04-27 19:42:47
我有以下需要导入的XML。我已经得到了用来填充orders表的SQL，但是现在我想要填充order_details表(带有文章)。
对于对订单的查询，请使用以下命令：
FROM OPENXML(@hDoc, 'orders/mail/order')但是，在第二个查询中，我无法获得所有项目的记录集(带有父订单)。
我希望我能用：
FROM OPENXML(@hDoc, 'orders/mail/order/article')但没什么回报。
<orders>
<mail>
<set id="s" sequence="1" version="1.1" >
<property id="calc_vat" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[1]]></property>
<set id="order" sequence="4" version="">
<property id="total" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[€ 169,46]]></property>
<set id="article" sequence="1" version="">
<property id="basepricevalue" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[155.66]]></property>
</set>
<set id="shipping" sequence="2" version="">
<property id="cost-list-used" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[none]]></property>
<property id="desc" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[<h5><u><span style("font-family:tahoma,geneva,sans-serif"><strong>Voor leveringen en datum bel:]]></property>
</set>
</set>
</set>
</mail>
</orders>回答 1
Stack Overflow用户
回答已采纳
发布于 2017-04-27 23:15:19
您需要提供更多的样本数据和最终结果应该是什么样子。根据你所说的，这应该有助于你开始工作。
-- your XML (Note: I added a second "article" for a better sample)
DECLARE @hDoc XML =
'<orders>
<mail>
<set id="s" sequence="1" version="1.1" >
<property id="calc_vat" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[1]]></property>
<set id="order" sequence="4" version="">
<property id="total" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[€ 169,46]]></property>
<set id="article" sequence="1" version="">
<property id="basepricevalue" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[155.66]]></property>
</set>
<set id="article" sequence="2" version="">
<property id="basepricevalue" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[465.99]]></property>
</set>
<set id="shipping" sequence="2" version="">
<property id="cost-list-used" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[none]]></property>
<property id="desc" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[<h5><u><span style("font-family:tahoma,geneva,sans-serif"><strong>Voor leveringen en datum bel:]]></property>
</set>
</set>
</set>
</mail>
</orders>';
-- Possible solution(depending on what you need your final output to look like):
SELECT
--nd.x.value('(@id)[1]', 'varchar(8000)'),
--nd2.x.value('(@id)[1]', 'varchar(1000)'),
orderSequence = nd.x.value('(@sequence)[1]', 'varchar(100)'),
orderProperty = nd.x.value('(property/@id)[1]', 'varchar(8000)'),
orderPropertyValue = nd.x.value('(property/text())[1]', 'varchar(8000)'),
articleSequence = nd2.x.value('(@sequence)[1]', 'varchar(100)'),
articleProperty = nd2.x.value('(property/@id)[1]', 'varchar(100)'),
articlePropertyValue = nd2.x.value('(property/text())[1]', 'varchar(100)')
FROM (VALUES (@hDoc)) hdoc(orders)
CROSS APPLY orders.nodes('orders/mail//set[@id="order"]') nd(x)
CROSS APPLY x.nodes('set[@id="article"]') nd2(x);结果：
orderSequence orderProperty orderPropertyValue articleSequence articleProperty articlePropertyValue
------------- ------------- ------------------ --------------- --------------- --------------------
4 total € 16 1 basepricev 155.66
4 total € 16 2 basepricev 465.99同样，如果这没有帮助，请张贴更多的样本数据和您想要的最终输出是什么样子。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43666631
复制相关文章
相似问题
腾讯云开发者
