Select Multiple XML nodes in SQL Server to append values

Question

I am trying to append some nodes to an existing XML in the database. The XML looks like the following:

<Parameters>
  <Parameter>
    <Name>ColumnsToDisplay</Name>
    <Value>Priority</Value>
    <Value>Description</Value>
    <Value>Asset ID</Value>
  </Parameter>
</Parameters>

The number of values can vary for each parameter. I'm trying to update the XML by appending a few new values as with the query below. Its a little wonky but it is the only way I could get FOR XML AUTO to work.

set @NewXML =
(
select * from 
    (
    SELECT  
        Parameter.C.value('Name[1]', 'varchar(256)') as Name,
        Parameter.C.value('Value[1]', 'varchar(256)') AS Value
    FROM tblSavedReportParameters
    CROSS APPLY ParameterXML.nodes('//Parameter') Parameter(C)
    where savedreportid = @SavedReportID
    union 
    select  'FilterType' as Name, '#All' as Value
    union 
    select  'FilterText' as Name, '#All' as value
    ) as Parameter for xml Auto, elements, root('Parameters') 
)

Everything works great except the new XML only returns the first value for each parameter. I've tried a variety of CROSS APPLY and INNER JOINS on the value node and just can't get the syntax down. It currently looks like this:

<Parameters>
  <Parameter>
    <Name>ColumnsToDisplay</Name>
    <Value>Priority</Value>
  </Parameter>
  <Parameter>
    <Name>FilterText</Name>
    <Value>#All</Value>
  </Parameter>
  <Parameter>
    <Name>FilterType</Name>
    <Value>#All</Value>
  </Parameter>
</Parameters>

How can I update the query to return all the values for each parameter? Thanks!

Mikael Eriksson · Accepted Answer · 2013-09-12 05:32:44Z

1

set @NewXML = 
(
  select (
         select T.ParameterXML.query('Parameters/Parameter')
         from tblSavedReportParameters as T
         where T.savedreportid = @SavedReportID 
         ),
         (
         select P.Name, P.Value
         from (
              select 'FilterType' as Name, '#All' as Value
              union all
              select 'FilterText', '#All'
              ) as P
         for xml path('Parameter'), type
         )
  for xml path('Parameters')
)

This query builds two XML columns that is then combined using for xml path('Parameters'). The first column contains all Parameters from the XML and the second column contains all your new Parameters.

edited Sep 12, 2013 at 5:32

answered Sep 12, 2013 at 5:21

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Korben Dallas Over a year ago

Wow, much easier to do it that way. I'll keep that in mind for the future. Thanks!

Collectives™ on Stack Overflow

Select Multiple XML nodes in SQL Server to append values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related