读取SQL Select语句中的XML值

Question

我试图从SQL Server列提取XML值并将其放入SQL语句。我的问题是，我找到的文档没有解释如果XML路径中有空格或""如何做到这一点。

我试图在这里显示的XML中提取value属性(XML中没有名称空间)。SQL Server列名为Settings:

<properties>
<settings hwid="stream:0.0.0">
<setting typeid="78622C19-58AE-40D4-8EEA-17351F4273B6">
<name>Codec</name>
<value>4</value>
</setting>
</settings>
</properties>

Answer 1

您可以使用OPENXML从xml检索数据，首先创建这样的过程:

CREATE PROCEDURE GetXmlValueProc
@xml NVARCHAR(max)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;
DECLARE @Result NVARCHAR(50);
SELECT  value
FROM
OPENXML(@hdoc, '/properties/settings/setting', 2)
WITH
(
value VARCHAR(100)
);
EXEC sp_xml_removedocument @hdoc;
END
GO

并以这种方式调用过程:

DECLARE @xml NVARCHAR(MAX)='<properties><settings hwid="stream:0.0.0"><setting typeid="78622C19-58AE-40D4-8EEA-17351F4273B6"><name>Codec</name><value>4</value></setting></settings></properties>'
EXEC dbo.GetXmlValueProc @xml

甚至你可以使过程更通用，并传递xml路径来获取数据。

Answer 2

我在XML中没有看到任何空格。如果您指的是各种属性，例如hwid，那么这些属性将与节点名称分开解析。您可以通过以@作为前缀来选择。

我假设value节点的类型是int，如果不是，您可以在下面更改它:

SELECT
t.Settings.value('(/properties/settings/setting/value)[1]', 'int'),
t.Settings.value('(/properties/settings/setting/@typeid)[1]', 'uniqueidentifier'),
t.Settings.value('(/properties/settings/@hwid)[1]', 'nvarchar(max)')
FROM myTable t

---

作为参考，如果您确实有一个带有空格的节点:它将被编码为 和双引号作为"