sql-server-2008 - SQL - 使用 INNER JOIN 获取 XML，修改 XML

Question

我在 SQL Server 2008 中有一个表，它在 XML 列中存储 XML 数据。一个典型的 XML 片段是:

<validation>
    <Field1 author="56234" date="20120101" />
    <Field2 author="23232" date="20120101" />
        [...etc...]
</validation>

我正在尝试解决的问题——我不知道该怎么做——是通过 INNER JOIN 将此数据选择到另一个表并修改结果集中的 XML 数据，即，所以我得到这个:

<validation>
    <Field1 author="56234" date="20120101" authorFullName="Bob Smith" />
    <Field2 author="23232" date="20120101" authorFullName="Jane Hill" />
        [...etc...]
</validation>

现在我知道我可以执行CROSS APPLY 将 XML 数据直接拉入记录集并进行内部连接 ​​- 例如:

select xmldata.a, people.personname
    from xmldata
        cross apply xmldata.x.nodes('/validation/node()') vdata(fielddata)
        inner join people 
            on people.personid = vdata.fielddata.value('@author','NVARCHAR(20)')

但我实际上想要做的是返回原始 XML，但添加了一个新属性，将 people.PersonName 映射到新的@authorFullName 属性。

我不能完全理解语法(或者即使它确实可能)。我假设我会cross apply做一个modify with insert attribute - something based on

select xmldata.a, xmldata.x
    from xmldata
        cross apply xmldata.x.modify('insert attribute authorFullName {sql:column("people.personfullname")} into /validation/node()')
        inner join people 
            on people.personid = [...what goes here?...]

但是让语法正确是逃避我。我越来越认为这是不可能的，我最好在两个查询中执行此操作并将结果合并到非 SQL 业务逻辑中。

Answer 1

您不能在 select 语句中使用 modify。

来自 modify() Method (xml Data Type)

The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

我认为您有两个选择。

1. 分解 XML 并使用 for xml path 以您想要的方式重建 XML 文档，并在适当的位置插入人名。
2. 将 XML 提取到一个变量并使用 set @XML.modify(insert... 插入人名。

如果选择第二个选项，则必须使用 while 循环，因为 insert (XML DML) 中的 expression2|必须是单个节点。

选项 2 的代码可能如下所示。

declare @XML xml
declare @C int
declare @PersonName varchar(50)
declare @PersonID int

-- Store your XML in a XML variable
set @XML = 
'<validation>
    <Field1 author="56234" date="20120101" />
    <Field2 author="23232" date="20120101" />
</validation>'

-- Get number of nodes to be modified
set @C = @XML.value('count(/validation/*)', 'int')

-- For each node
while @C > 0
begin
  -- Get person id from XML
  set @PersonID = @XML.value('(/validation/*[sql:variable("@C")]/@author)[1]', 'int')

  -- Get person name
  select @PersonName = personname
  from people
  where personid = @PersonID 

  if @@rowcount = 1
  begin
    -- add person name to XML
    set @XML.modify('insert attribute authorFullName {sql:variable("@PersonName")} 
                        into (/validation/*[sql:variable("@C")])[1]')
  end

  -- next node
  set @C = @C - 1
end