xml - 根据属性值从 SQL Server 2008 中删除 xml 节点

Question

我的数据库中有一个 xml 结构，如下所示:

<ArrayOfContactDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ContactDetails id="93679d1d-9feb-45d1-8356-e85d188fa34c">
    <contactid>93679d1d-9feb-45d1-8356-e85d188fa34c</contactid>
    <contactname>Name 1</contactname>
    <contactemail>Email 1</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="69f54067-edf9-414e-80b6-099ac471dc43">
    <contactid>69f54067-edf9-414e-80b6-099ac471dc43</contactid>
    <contactname>Name 2</contactname>
    <contactemail>Email 2</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="93144086-be1c-4f34-a5f7-6e8ac67c2121">
    <contactid>93144086-be1c-4f34-a5f7-6e8ac67c2121</contactid>
    <contactname>Name 3</contactname>
    <contactemail>Email 3</contactemail>
    <contactphonenumber>123456</contactphonenumber>
  </ContactDetails>
</ArrayOfContactDetails>

我正在尝试根据 ContactDetails id 属性删除 ContactDetails 节点。但我似乎遇到了一堵砖墙。

我的SP代码是这样的

UPDATE tableName 
SET tableField.modify('delete //ContactDetails[@id=sql:variable("@contactId")]') 
WHERE tableId = @tableId 

我在页面上或在调试/执行 sp 时没有收到任何错误，这让我疯狂地想知道为什么它不起作用!!

谢谢，汤姆

Answer 1

您的 @contactID 变量定义为什么？？

它在我的机器上工作 :-) 试试这个:

DECLARE @work TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @work VALUES(1, '<ArrayOfContactDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ContactDetails id="93679d1d-9feb-45d1-8356-e85d188fa34c">
    <contactid>93679d1d-9feb-45d1-8356-e85d188fa34c</contactid>
    <contactname>Name 1</contactname>
    <contactemail>Email 1</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="69f54067-edf9-414e-80b6-099ac471dc43">
    <contactid>69f54067-edf9-414e-80b6-099ac471dc43</contactid>
    <contactname>Name 2</contactname>
    <contactemail>Email 2</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="93144086-be1c-4f34-a5f7-6e8ac67c2121">
    <contactid>93144086-be1c-4f34-a5f7-6e8ac67c2121</contactid>
    <contactname>Name 3</contactname>
    <contactemail>Email 3</contactemail>
    <contactphonenumber>123456</contactphonenumber>
  </ContactDetails>
</ArrayOfContactDetails>')

DECLARE @contactID VARCHAR(50) = '69f54067-edf9-414e-80b6-099ac471dc43'

UPDATE @work 
SET XmlContent.modify('delete //ContactDetails[@id=sql:variable("@contactId")]') 
where id = 1

SELECT * FROM @Work WHERE id = 1

我返回的结果 XML 没有那个 <ContactDetails> 节点。

您的 @contactID 是否偶然定义为 UNIQUEIDENTIFIER？您必须将其转换为 varchar - XML 操作都适用于字符串....

PS:我刚刚注意到的另一件事 - 这也行不通:

DECLARE @YourOriginalContactID UNIQUEIDENTIFIER 
SET @YourOriginalContactID = '69f54067-edf9-414e-80b6-099ac471dc43'

DECLARE @ContactID VARCHAR(50)
SET @ContactID = CAST(@YourOriginalContactID AS VARCHAR(50))

这会失败，因为 CAST 操作将 GUID 转换为大写字符串......您需要再次将其转换为小写:

SET @ContactID = LOWER(CAST(@YourOriginalContactID AS VARCHAR(50)))

然后 它再次起作用了!相当棘手....