如何在SQL中获取XML元素和属性值?
问题描述:
我需要从XML获得所有值要么呈现元素或属性。如何在SQL中获取XML元素和属性值?
例子:
DECLARE @XML = '<root>
<row1 attr1="x">1</row1>
<row2 attr2="x">2</row2>
</root>'
在这里,我的预期输出:
Column Value
---------------------
row1 1
attr1 x
row2 1
attr2 x
我能够得到的元素,并通过使用下面的查询单独的属性值。
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/*') node(element)
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/row1/@*') node(element)
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/row2/@*') node(element)
但是,我需要在单个查询中获取值。
我上网的方法很多,但我不能得到这个需求的解决方案。
有没有办法做到这一点?
答
试试这个UNION ALL
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/*') node(element)
UNION ALL
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/row1/@*') node(element)
UNION ALL
SELECT
element.value('local-name(.)', 'VARCHAR(50)'),
element.value('.', 'VARCHAR(100)')
FROM
@OldXML.nodes('/root/row2/@*') node(element)
+0
我试过了。但是,这个查询也会影响性能。 – LTA
答
我不知道甲骨文如何深刻支持XQuery
。你可能会改变你这样的XML(SQL-Server的语法):
DECLARE @XML XML = '<root>
<row1 attr1="x" attrAdd="test">1</row1>
<row2 attr2="y">2</row2>
<row3>3</row3>
</root>';
SELECT @XML.query
(
N'
<root>
{
for $e in /root/*
return <element type="element" name="{local-name($e)}" value="{$e/text()}"/>
}
{
for $a in /root/*/@*
return <element type="attr" parent="{local-name($a/..)}" name="{local-name($a)}" value="{$a}"/>
}
</root>
'
);
结果
<root>
<element type="element" name="row1" value="1" />
<element type="element" name="row2" value="2" />
<element type="element" name="row3" value="3" />
<element type="attr" parent="row1" name="attr1" value="x" />
<element type="attr" parent="row1" name="attrAdd" value="test" />
<element type="attr" parent="row2" name="attr2" value="y" />
</root>
另外,嵌套的方法将允许保留元素之间的关系和属性:
SELECT @XML.query
(
N'
<root>
{
for $e in /root/*
return
<element tag="{local-name($e)}" value="{$e/text()}">
{
for $a in $e/@*
return <attribute tag="{local-name($a)}" value="{$a}"/>
}
</element>
}
</root>
'
)
结果
<root>
<element tag="row1" value="1">
<attribute tag="attr1" value="x" />
<attribute tag="attrAdd" value="test" />
</element>
<element tag="row2" value="2">
<attribute tag="attr2" value="y" />
</element>
<element tag="row3" value="3" />
</root>
种
这两种方法都将允许读取表格中的数据。
XML的支持是**高度特定供应商的** - 所以请添加标签到指定是使用了'mysql','postgresql','SQL-server','oracle'或'db2' - 或完全是另一回事。 –