添加/修改 XML 列，具有来自另一列值的属性，将结果作为具有其他字段的列返回 [英] Add/modify XML column, with attributes from another column value, Return results as column with other fields

问题描述

我有一个 XML 列，我们称之为XmlField"，例如:

I have an XML column, lets call it 'XmlField' that looks like this, as an example:

<Node Attrib="9">Name1</Node>
<Node Attrib="100">Name2</Node>
<Node Attrib="101">Name2</Node>

示例:

    select
      (list of fields)
    from
      TableX , TableY         -- TableX has the XMLcolumn    
     CROSS APPLY TableX.XmlField.nodes('/Node') m1(xmlcol)
    where
    m1.xmlcol.value('@Attrib', 'int') = TableY.IntField        

问题:我必须从TableY"中检索不同的列/列集并将其附加到返回的此 XML 数据中，例如作为属性，满足上述条件.我不需要将它存储在任何表中，只是为了检索 XmlField(或任何其他临时数据等)看起来更新:

Problem: I have to retrieve a different column/set of columns from 'TableY' and append it to this XML data returned, say as an attribute, where above condition is met. I don't need to store this in any table, just for retrieving the XmlField (or any other temp data etc.) to look updated:

 <Node Attrib="9" OtherField="SomeValue">Name1</Node>
 <Node Attrib="100" OtherField="SomeValue2">Name1</Node>

其中OtherField"是TableY"的一列，用于更新查询返回的XmlField"(不存储在表本身中)

Where 'OtherField' is a column of 'TableY' is used to update the 'XmlField' being returned by the query (not stored in table itself)

select
    TableY.IntField  as '@Attrib', 
    TableY.OtherField as '@OtherField'
        from
         TableY        ,  TableX 
         CROSS APPLY TableX.XmlField.nodes('/Node') m1(xmlcol)
        where
        m1.xmlcol.value('@Attrib', 'int') = TableY.IntField  
    FOR XML PATH('Node'), TYPE

现在我还需要返回此查询以及其他列.试图将其作为子查询嵌套是行不通的.我必须将它嵌套在另一个 XML blob 中，该 blob 也将所有其他列作为 XML 返回.

Now I need to return this query along with other columns as well. Trying to nest this as a sub-query doesn't work. I have to make it nested inside another XML blob that returns all the other columns as XML as well.

这样的事情(删除变量集并尝试直接检索也不起作用)

Something like this (Removing the variable set and trying to directly retrieve doesn't work too)

DECLARE @result XML
SELECT @result = 
(SELECT (list of columns)
   , 
   (Select
    TableY.IntField  as '@Attrib', 
    TableY.OtherField as '@OtherField'
        from
         TableY        ,  TableX 
         CROSS APPLY TableX.XmlField.nodes('/Node') m1(xmlcol)
        where
        m1.xmlcol.value('@Attrib', 'int') = TableY.IntField  
    FOR XML PATH('Node'), TYPE) --AS 'XmlField'
, (some more columns)
FROM TableX
  -- Do some joins (this needs to be satisfied for subquery that builds up the updated XML'column')
  -- Check for some conditions
   FOR XML PATH('Root'), TYPE); 

我应该如何将这个更新的属性作为另一列返回?我还需要确保子查询正确完成了外部连接和条件检查，有没有办法确保这一点?

How should I return this updated attributes as just another column? I also need to make sure the external joins and condition checks are done correctly by the sub-query, is there a way to ensure this as well?

推荐答案

declare @TableX table (ID int identity, XmlField xml)

insert into @TableX values
(
'<Node Attrib="9">Name1</Node>
 <Node Attrib="100">Name2</Node>
 <Node Attrib="101">Name2</Node>'
)

insert into @TableX values
(
'<Node Attrib="9">Name1</Node>
 <Node Attrib="101">Name2</Node>'
)

insert into @TableX values
(
'<Node Attrib="1">Name1</Node>'
)

declare @TableY table (IntField int, OtherField varchar(15))

insert into @TableY values 
(9, 'SomeOtherValue1'),
(100, 'SomeOtherValue2'),
(101, 'SomeOtherValue3')

;with C as
(
  select X.ID,
         Y.IntField as Attrib,
         Y.OtherField as OtherField
  from @TableX as X
    cross apply X.XmlField.nodes('/Node') as T(N)
    inner join @TableY as Y
      on T.N.value('@Attrib', 'int') = Y.IntField
)
select (select C2.Attrib as '@Attrib',
               C2.OtherField as '@OtherField'
        from C as C2
        where C1.ID = C2.ID
        for xml path('Node'), type) as XMLField       
from C as C1
group by ID

结果:

XMLField
<Node Attrib="9" OtherField="SomeOtherValue1" /><Node Attrib="100" OtherField="SomeOtherValue2" /><Node Attrib="101" OtherField="SomeOtherValue3" />
<Node Attrib="9" OtherField="SomeOtherValue1" /><Node Attrib="101" OtherField="SomeOtherValue3" />

这篇关于添加/修改 XML 列，具有来自另一列值的属性，将结果作为具有其他字段的列返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！