时间戳

首页 > 解决方案 > 没有 XML 标记的 XML 列的返回值

json - 没有 XML 标记的 XML 列的返回值

问题描述

我试图在 SQL Server 2016 中不使用 convert 或 cast 来查找 XML 列中特定字段的值，但是 XML 不包含传统标记。我对 XML 数据不太熟悉，所以我试图了解如何最好地实现这一点。

我试过使用[column].value，但是我无法返回结果，因为我不知道如何识别没有标签的节点。

这是我正在运行的查询：

select customeridentifier, customerdemographics 
from customerdatafile

customerdemographics 列是 xml，如下所示：

{  
  "request": {  
    "customer_id": "12345efg"
    "ip_address": "12.1.4.12"
    "name.value": "JAMES"
  },
  "customer_compatibility": 40
}

我希望返回“customer_compatibility”的值，我试图通过以下方式完成：

customerdemographics.value('(customer_compatibility)[1]','nvarchar(max)')

它返回一个空值。另一方面，

customerdemographics.value('(.)','nvarchar(max)')

毫无问题地返回 XML 字段的完整值。

customer_compatibility从 XML 列返回值的最有效方法是什么？最坏的情况，我可以进行转换/转换，但由于数据大小，查询会很慢。

标签： jsonsql-serverxml

解决方案

列中的数据customerdemographics似乎是格式的（尽管对之间JSON有缺失）。SQL Server 2016 支持并且您可以尝试使用（从字符串中提取标量值）和（解析字符串并将对象和属性作为行和列返回的表值函数）来解析它。,key: valueJSONJSONJSON_VALUE()JSONOPENJSON()JSON

桌子：

CREATE TABLE customerdatafile (
   customeridentifier int,
   customerdemographics nvarchar(max)
)
INSERT INTO customerdatafile 
   (customeridentifier, customerdemographics)
VALUES
   (1, N'{"request": {"customer_id": "12345efg", "ip_address": "12.1.4.12", "name.value": "JAMES"}, "customer_compatibility": 40}')

提取单个值：

SELECT 
   customeridentifier, 
   JSON_VALUE(customerdemographics, '$.customer_compatibility') AS customer_compatibility
FROM customerdatafile

解析整个 JSON：

SELECT d.customeridentifier, j.*
FROM customerdatafile d
CROSS APPLY OPENJSON(d.customerdemographics) WITH (
   customer_id nvarchar(100) '$.request.customer_id',
   ip_address nvarchar(100) '$.request.ip_address',
   [name.value] nvarchar(100) '$.request."name.value"',
   customer_compatibility int '$.customer_compatibility'
) j

结果：

customeridentifier  customer_compatibility
1                   40

customeridentifier  customer_id ip_address  name.value  customer_compatibility
1                   12345efg    12.1.4.12   JAMES       40

推荐阅读