CODEWITHSUNDEEP
sql-serverxml
ca9163d9
ca9163d9

Reputation: 29159

Wrap XML output as a child element?

For example, I have the following SQL returning an Xml code <B><A Value="1"/></B>.

select
        1 [@Value]
    for xml path ('A'), root ('B')

Now I want to wrap it in <D><C>......</C></D> so the expected result will be <D><C><X><B><A Value="1"/></B></X></C></D>. I can do it by wrapping the sql as a subquery.

select
    (select
            1 [@Value]
        for xml path ('A'), root ('B'), type)
for xml path ('C'), root('D')

Is it a way to do it without subquery?

Upvotes: 0

Views: 56

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

In your given case it can be done like this:

SELECT  1 [X/B/A/@Value]
FOR XML PATH('C'), ROOT ('D')

The result

<D>
  <C>
    <X>
      <B>
        <A Value="1" />
      </B>
    </X>
  </C>
</D>

But there are (quite often) situations, where you need to wrap this in sub-queries.

Upvotes: 1

Related Questions