Add/modify XML column, with attributes from anothe

Question

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>

Example:

    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        

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>

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

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?

Answer 1

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

Result:

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" />