在SQL Server 2008中从XML字段中选择值

18 浏览
0 Comments

在SQL Server 2008中从XML字段中选择值

观察我的XML字段，我的行看起来是这样的：\n

JonJohnson
KathyCarter
BobBurns

\n请注意，这是我表中的三行。\n我想将SQL结果返回为一个表格，如下所示：\n

Jon  | Johnson
Kathy| Carter
Bob  | Burns

\n哪个查询可以实现这个目标？

0

3 答案

0 Comments

这篇文章将讨论在SQL Server 2008中从XML字段中选择值的问题以及解决方法。原问题的出现是因为XML的格式与给出的示例有些不同。XML包含了一个键的列表，并且将XML存储在名为DeleteBatch的表的SourceKeys列中。

首先，我们创建一个表并向其插入一些数据：

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)
INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1, 
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))
INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2, 
    (CAST('<k>100</k><k>101</k>' AS XML))

然后，我们使用以下SQL语句从XML中选择键：

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

这将得到以下查询结果：

ExecutionKey    Key
1   1
1   2
1   3
2   100
2   101

通过这种方式，我们可以从XML字段中选择出所需的值。

0
0 Comments

在SQL Server 2008中，如果要从XML字段中选择值，可以使用XML方法。使用xml.value()提取值，使用xml.nodes()投影节点，并使用CROSS APPLY进行连接。如果每个字段中都只包含一个元素"person"，则可以放弃使用nodes()和cross apply。如果XML是一个变量，可以使用FROM .nodes(...)进行选择，而不需要cross apply。

这种方法的效率如何以及是否有更好的方法，需要进一步研究。CROSS APPLY与XPath结果的组合可能会导致查询消耗大量资源。

对于提高XML性能的技术已经有很多文档进行了记录，可以参考msdn.microsoft.com/en-us/library/ms345118(SQL.90).aspx。

需要注意的是，如果XML中定义了xmlns命名空间，需要在上述XQuery（XPath）表达式中定义这些命名空间。

对于一个包含多行的XML列，可以使用上述方法循环遍历每行并从XML列中提取数据字段，然后将其放入插入语句中。

正如所说，命名空间非常重要。即使只是使用SELECT TOP 1，如果没有正确的命名空间，查询也会非常慢！

0
0 Comments

问题的原因是查询中使用了XQuery语法，但是该语法在SQL Server 2008中不被支持。解决方法是使用.nodes()和cross apply来处理包含多个<person>元素的xmlField字段。

具体的解决方案如下：

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

上述解决方案中，通过在查询中使用.nodes()和cross apply来处理包含多个<person>元素的xmlField字段，可以正确地返回数据。

然而，有用户报告在SQL Server 2008 R2 Express中使用该解决方案时遇到了错误：The XQuery syntax '/function()' is not supported.。而其他用户则报告该解决方案只返回了第一个XML记录的数据，并且提到了一个名为[myTable]的表，这让人感到困惑。

另外，还有用户提到他们尝试了多种方法，但是无法正确地从XML中选择值。他们的XML如下：<BAM><Type>Electrical</Type><BaIds><a:int>7330</a:int></BaIds></BAM>，他们的查询语句如下：select e.MessageData.value('(/BAM/Type)[1]', 'varchar(100)')。他们还尝试了其他多种组合，但是始终只得到了NULL。

最后，还有用户提到如果XML中给定了一个命名空间xmlns="http://bayerbbs.com/AMUNI"，则需要将其映射为默认命名空间WITH XMLNAMESPACES(DEFAULT 'http://bayerbbs.com/AMUNI') SELECT ...。

通过使用.nodes()和cross apply来处理包含多个<person>元素的XML字段，并注意处理命名空间，可以在SQL Server 2008中正确地从XML字段中选择值。

0