用XML字段更新SELECT语句

Question

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

我当前的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>

我一直得到一个返回的空值，但是我可以看到LocationID确实有一个值。

编辑:我的解决方案

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>'s 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)