我正在尝试在SQL Server 2016中解析和转换XML值。我不能使用sp_xml_preparedocument
或sp_xml_removedocument
内置过程,因为我想在表值函数中使用它。
我想选择这4个值:
- 测试XML。。。821327282
- 测试值
- 测试属性
- 测试属性
XML模板值:
<?xml version="1.0" encoding="utf-8"?>
<stns1:testXML...821327282 xmlns:stns1="qaTESTXML">
<myvalue>Test value</myvalue>
<myattribute>Test attribute</myattribute>
<myproperty>Test property</myproperty>
</stns1:testXML...821327282>
我的T-SQL try>代码:
select
[xml].[node].query('local-name(/ROOT[0]/id)') as [id],
[xml].[node].query('local-name(/ROOT[0]/myvalue)') as [value],
[xml].[node].query('local-name(/ROOT[0]/myattribute)') as [attribute],
[xml].[node].query('local-name(/ROOT[0]/myproperty)') as [property]
from
@xml.nodes('/') as [xml]([node])
试试这个:
DECLARE @XML XML = '<?xml version="1.0" encoding="utf-8"?>
<stns1:testXML...821327282 xmlns:stns1="qaTESTXML">
<myvalue>Test value</myvalue>
<myattribute>Test attribute</myattribute>
<myproperty>Test property</myproperty>
</stns1:testXML...821327282>'
-- you need to **include** and respect the XML namespace in your XML document!
;WITH XMLNAMESPACES('qaTESTXML' AS ns1)
SELECT
[node].value('(myvalue)[1]', 'varchar(50)') as [value],
[node].value('(myattribute)[1]', 'varchar(50)') as [attribute],
[node].value('(myproperty)[1]', 'varchar(50)') as [property]
FROM
@xml.nodes('/ns1:testXML...821327282') as [xml]([node])
不确定选择testXML...821327282
值是什么意思-这是一个XML元素/节点-而不是一个值。。。。。
更新:如果您需要获得;name";对于根节点元素-您可以使用此-BE AWARE使用//myvalue
方法对查询性能来说非常糟糕,尤其是在较大的XML文档上!你已经被警告了!
;WITH XMLNAMESPACES('qaTESTXML' AS ns1)
SELECT
[node].value('local-name(.)[1]', 'varchar(50)') as rootnode,
[node].value('(//myvalue)[1]', 'varchar(50)') as [value],
[node].value('(//myattribute)[1]', 'varchar(50)') as [attribute],
[node].value('(//myproperty)[1]', 'varchar(50)') as [property]
FROM
@xml.nodes('/ns1:*') as [xml]([node])