Sql Server XQuery transform xml in select clause

Question

Is there some way to transform/project XML from an XML column in the select clause of an Sql Server query?

Lets say we have a table with Id:Guid , Data:XML .

And lets say we have the following data in the "Data" column:

<Order>
  <Details>
     <Detail>
         <Quantity>10</Quantity>
         <ItemPrice>20</Quantity>
     </Detail>
     <Detail>
         <Quantity>10</Quantity>
         <ItemPrice>20</Quantity>
     </Detail>
  </Details>
</Order>

is there any way to project this (using a select + xquery) into , say for example:

<Order>
  <Details>
     <Detail>
         <LineTotal>200</LineTotal>
     </Detail>
     <Detail>
         <LineTotal>200</LineTotal>
     </Detail>
  </Details>
</Order>

I'm not interested in how this can be done in memory of my app, I want a server side transform in the select clause in the sql query.

You meant <ItemPrice>20</ItemPrice> instead of <ItemPrice>20</Quantity> — RichardTheKiwi
– RichardTheKiwi, Commented Mar 2, 2011 at 8:05

RichardTheKiwi · Accepted Answer · 2011-03-02 08:14:37Z

5

Produces exactly the output you want, but SQL Server XML is quite limited. In this example, you really need to know exactly how to (re)produce the entire tree - with the variation "hand coded".

declare @tbl table (id uniqueidentifier, data xml)
insert @tbl select newid(), '
<Order>
  <Details>
     <Detail>
         <Quantity>10</Quantity>
         <ItemPrice>20</ItemPrice>
     </Detail>
     <Detail>
         <Quantity>10</Quantity>
         <ItemPrice>20</ItemPrice>
     </Detail>
  </Details>
</Order>'

select ((
    select d.d.value('(Quantity)[1]','int') * d.d.value('(ItemPrice)[1]','int') [LineTotal]
    from ds.ds.nodes('Detail') d(d)
    for xml path('Detail'), type)) Details
from @tbl t
cross apply data.nodes('Order/Details') ds(ds)
for xml path('Order')

answered Mar 2, 2011 at 8:14

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

Collectives™ on Stack Overflow

Sql Server XQuery transform xml in select clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related