查询XML列中的序列化字典

问题描述：

我有一个字典，我在C＃中序列化并存储在MSSQL服务器上的XML列中。序列化的XML如下所示：查询XML列中的序列化字典

<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
    <KeyValueOfstringanyType> 
    <Key>code</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">WFR 052</Value> 
    </KeyValueOfstringanyType> 
    <KeyValueOfstringanyType> 
    <Key>type</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">Newsletter</Value> 
    </KeyValueOfstringanyType> 
</ArrayOfKeyValueOfstringanyType>

最终，我想获取值，其中密钥是“代码”。我采取的第一步是获取第一个值，而不管关键。

SELECT [xml_column].value('(/ArrayOfKeyValueOfstringanyType/KeyValueOfstringanyType/Value)[1]','varchar(255)') as val 
FROM [my_table]

返回空值。我知道它与命名空间有关，因为当我尝试删除命名空间的同一个查询时，我得到一个值。我已经看到了一些名称空间的其他场景，但是我的XML格式有点不同，我正在努力寻找正确的语法。

这里是我看着另一个问题：

XML Field - Query

答

您所遇到的问题是由于您的XML模式规范。如果XML文档中的节点是架构的一部分，则在查询该节点时必须指定该架构。或者，您可以为模式规范使用通配符。但是，在没有模式的情况下指定节点名称不起作用（正如你所经历的那样）。

因此，让我们来看一个例子：

SQL Fiddle

MS SQL Server 2008的架构设置：

CREATE TABLE dbo.Tbl(id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, dict XML); 

INSERT INTO dbo.Tbl(dict) 
VALUES('<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
     <KeyValueOfstringanyType> 
     <Key>code</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">WFR 052</Value> 
     </KeyValueOfstringanyType> 
     <KeyValueOfstringanyType> 
     <Key>type</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">Newsletter</Value> 
     </KeyValueOfstringanyType> 
    </ArrayOfKeyValueOfstringanyType>');

表dbo.Tbl与刚刚两列创建一个身份id列和XML的dict列。

为了让您的第一个查询工作，指定使用通配符每个节点的架构：

查询1：返回

SELECT dict.value('/*:ArrayOfKeyValueOfstringanyType[1]/*:KeyValueOfstringanyType[1]/*:Key[1]','NVARCHAR(MAX)') 
FROM dbo.Tbl;

导致第一Key：

Results：

| COLUMN_0 | 
|----------| 
|  code |

现在，您希望返回Key ='code'处的所有键值对的Value节点。你可以在xquery中进行过滤，但我通常更喜欢用SQL进行过滤。为此，我们首先需要获取所有对。该XML节点功能会让我们更接近了一步：

查询2：

SELECT id,key_value.query('.') 
    FROM dbo.Tbl 
CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value);

它返回每一个行KeyValueOfstringanyType节点：

Results：

| ID |                                                                          COLUMN_1 | 
|----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 
| 1 | <p1:KeyValueOfstringanyType xmlns:p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"><p1:Key>code</p1:Key><p1:Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="d3p1:string">WFR 052</p1:Value></p1:KeyValueOfstringanyType> | 
| 1 | <p1:KeyValueOfstringanyType xmlns:p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"><p1:Key>type</p1:Key><p1:Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="d3p1:string">Newsletter</p1:Value></p1:KeyValueOfstringanyType> |

使用那我们可以去Key和Value使用XML.value功能： 查询3：

SELECT id, 
     key_value.value('./*:Key[1]','NVARCHAR(MAX)') AS [key], 
     key_value.value('./*:Value[1]','NVARCHAR(MAX)') AS [value] 
    FROM dbo.Tbl 
CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value);

现在我们必须为每个键值对的行与键和在单独的列的值：

Results：

| ID | KEY |  VALUE | 
|----|------|------------| 
| 1 | code | WFR 052 | 
| 1 | type | Newsletter |

从WHERE子句中可以很容易地应用额外的过滤器：

查询4：

WITH KeyValues AS(
SELECT id, 
     key_value.value('./*:Key[1]','NVARCHAR(MAX)') AS [key], 
     key_value.value('./*:Value[1]','NVARCHAR(MAX)') AS [value] 
    FROM dbo.Tbl 
CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value) 
) 
SELECT * 
    FROM KeyValues 
WHERE [Key] = 'code';

Results：

| ID | KEY | VALUE | 
|----|------|---------| 
| 1 | code | WFR 052 |

这是很清楚的，谢谢。 – emarcee

+1好的详细答案，仍然我会添加按键过滤节点（）函数 –

答

塞巴斯蒂安MEINE很好的回答，虽然做这样的查询时，我更喜欢以滤除XPATH内部节点的数据（）功能，如下所示：

select 
    a.c.value('*:Value[1]','nvarchar(max)') as [value] 
from Table1 as t 
    outer apply t.Data.nodes(' 
     *:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType[*:Key="code"]' 
    ) as a(c)

它通常比解析完整的xml更快，然后通过where子句过滤所需的密钥。

sql fiddle example

查询XML列中的序列化字典

相关推荐