使用 XML 字段更新 SELECT 语句

Question

我正在尝试执行一个 UPDATE 语句，我需要从我连接的表中选择一个值。 我需要获取的值是 XML 节点的一部分，但我很难获得它。 我想从下面提到的XML中获取位置ID。 我编辑了代码和表名/字段，但前提保持不变。

我当前的 SQL 查询：

UPDATE table1
SET LocationId = CAST(t2.CustomProperties AS xml).value('(/ArrayOfCustomProperty/CustomProperty/@LocationId)[1]', 'varchar(36)')
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.x = t2.x
<?xml version="1.0" encoding="utf-16"?>  <ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>ShipToId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>CustomerId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>CorporateId</Key>      <Value>X</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>NeedsApproval</Key>      <Value>0</Value>    </CustomProperty>    <CustomProperty>      <DeveloperId>X</DeveloperId>      <Key>LocationId</Key>      <Value>X</Value>    </CustomProperty>  </ArrayOfCustomProperty>

我不断返回一个 NULL 值，但我可以看到位置 ID 确实有一个值。

编辑：我的解决方案

CAST(CAST(CustomProperties AS xml).query('ArrayOfCustomProperty/CustomProperty/Key[text()="LocationId"]/../Value/text()') AS nvarchar)

Answer 1

您的 XML 似乎是典型的键值对列表。

如果这在您的控制之下，您确实应该将其存储为本机 XML...将 XML 存储在字符串中既不规则又慢...

可以使用如下所示的查询来筛选所需的值：

DECLARE @DeveloperId VARCHAR(10)='X';
DECLARE @Key VARCHAR(50)='LocationId';
SELECT CAST(t2.CustomProperties AS xml)
.value('(/ArrayOfCustomProperty
/CustomProperty[(DeveloperId/text())[1]=sql:variable("@DeveloperId")
and (Key/text())[1]=sql:variable("@Key")]
/Value
/text())[1]','nvarchar(max)');

路径将寻找合适的<CustomProperty>并采取其<Values>text()

Answer 2

你可以这样尝试：

SET LocationId = (SELECT TOP 1 x.value('/LocationId[1]') AS [LocationId]
FROM CAST(t2.CustomProperties AS xml).nodes('/ArrayOfCustomProperty/CustomProperty') AS a(x)
WHERE x.value('/LocationId[1]') IS NOT NULL)