SQL Server - OPENXML 如何获取属性值 [英] SQL Server - OPENXML how to get attribute value
本文介绍了SQL Server - OPENXML 如何获取属性值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下 XML:
<Field FieldRowId="1000">
<Items>
<Item Name="CODE"/>
<Item Name="DATE"/>
</Items>
</Field>
我需要使用 OPENXML 获取 FieldRowId.到目前为止我拥有的 SQL:
I need to get the FieldRowId using OPENXML. The SQL i have so far:
INSERT INTO @tmpField
([name], [fieldRowId])
SELECT [Name], --Need to get row id of the parent node
FROM OPENXML (@idoc, '/Field/Items/Item', 1)
推荐答案
我向 xml 添加了一个根节点.并演示了如何抓取 ID.我假设您在 xml 中有多个字段元素.这假设您有起始 XML;你是否给了一个 Item 并且必须向上遍历?
I added a root node to the xml. and demonstrated grabbing the ID. I'm assuming you have more than one field element in the xml. This is assuming you have the starting XML; are you given an Item and have to traverse upwards?
DECLARE @T varchar(max)
SET @T =
'<root>
<Field FieldRowId="1000">
<Items>
<Item Name="CODE"/>
<Item Name="DATE"/>
</Items>
</Field>
<Field FieldRowId="2000">
<Items>
<Item Name="CODE"/>
<Item Name="DATE"/>
</Items>
</Field>
</root>'
DECLARE @X xml
SET @X = CAST(@T as xml)
SELECT Y.ID.value('../../@FieldRowId', 'int') as FieldID,
Y.ID.value('@Name', 'varchar(max)') as "Name"
FROM @X.nodes('/root/Field/Items/Item') as Y(ID)
这篇关于SQL Server - OPENXML 如何获取属性值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文