T-SQL: How to select rows as XML element

Question

I have table

   Cost       RenderedValue    SectionName
   ------------------------------------
  100.00      1000.00          Section1
  200.00      2000.00          Section2
  300.00      3000.00          Section3
  400.00      4000.00          Section4

and I want to produce this XML:

<Root>
   <Section1Cost>100.00</Section1Cost>
   <Section1RenderedValue>1000.00</Section1RenderedValue>
   <Section2Cost>200.00</Section2Cost>
   <Section2RendredValue>2000.00</Section2RendredValue>
</Root>

I was able to produce it using the TSQL ( Test is my table name)

SELECT
    (SELECT Cost AS 'Cost'FROM Test WHERE SectionName = 'Section1') AS 'Section1Cost',
    (SELECT RenderedValue AS 'Cost'FROM Test WHERE SectionName = 'Section1') AS 'Section1RenderedValue',
    (SELECT Cost AS 'Cost'FROM Test WHERE SectionName = 'Section2') AS 'Section2Cost',
    (SELECT RenderedValue AS 'Cost'FROM Test WHERE SectionName = 'Section2') AS 'Section2RendredValue'
FOR XML Path(''), Root('Root')

But this is ugly and I think it is not optimized . Can it be more elegant or whatever I have is correct?

I may have at most 30 rows in that Test table

Paul Hendrickson · Accepted Answer · 2016-01-07 20:05:04Z

1

This gives you XML that's slightly different (using the Section tags), but the SQL script should be more efficient:

SELECT
    Test.SectionName AS [@SectionName],
    Test.Cost AS [Cost],
    Test.RenderedValue AS [RenderedValue]

FROM Test
ORDER BY Test.SectionName
FOR XML PATH ('Section'), ROOT('Sections');

This is the output from that script for the sample table you provided:

<Sections><Section SectionName="Section1"><Cost>100.0000</Cost><RenderedValue>1000.0000</RenderedValue></Section><Section SectionName="Section2"><Cost>200.0000</Cost><RenderedValue>2000.0000</RenderedValue></Section><Section SectionName="Section3"><Cost>300.0000</Cost><RenderedValue>3000.0000</RenderedValue></Section><Section SectionName="Section4"><Cost>400.0000</Cost><RenderedValue>4000.0000</RenderedValue></Section></Sections>

answered Jan 7, 2016 at 20:05

Paul Hendrickson

462 bronze badges

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

1 Comment

LP13 Over a year ago

no that's not what I'm looking for. I cannot change the structure of the xml.

LP13 · Accepted Answer · 2016-01-08 18:03:42Z

0

I think I got it

EDIT1

I want to take back what I said, the solution I mentioned doesn't work exactly the way I want. When the record doesn't exists I want to print 0. So if modify the above it did not work

 select 
 case when t.sectionname = 'section1' then t.cost else 0 end as 'section1cost',
 case when t.sectionname = 'section1' then t.renderedvalue else 0 end as 'section1rendervalue'
 from test t
 for xml path(''),root('root')

edited Jan 8, 2016 at 18:03

answered Jan 7, 2016 at 22:05

LP13

34.8k63 gold badges267 silver badges473 bronze badges

3 Comments

Fuzzy Over a year ago

But this will not dynamically name the XML tags for you, as in section1, section2 etc,, or is that not needed?

LP13 Over a year ago

no the xml element are fixed and the element name is based on section name

Fuzzy Over a year ago

Thanks for the comment. I think I have the exact solution you need. See my newest answer.

Fuzzy · Accepted Answer · 2016-01-08 18:52:45Z

0

Hi here is a modified version of my previous answer based on your latest answer user3862378

DECLARE @sql varchar(max) = ''
DECLARE @case VARCHAR(MAX) = ''

SELECT  @case += 'case when t.sectionname = '''+SectionName+''' then t.cost  end as '''+SectionName+'Cost'',
 case when t.sectionname = '''+SectionName+''' then t.renderedvalue  end as '''+SectionName+'RenderedValue'','

  FROM <YOUR TABLE NAME>

  SELECT @sql = 'select '+ SUBSTRING(@case,1,LEN(@case)-1) +'from <YOUR TABLE NAME> t
 for xml path(''''),root(''root'')'

 EXECUTE (@sql)

And here is the results:

<root>
  <Section1Cost>100.00</Section1Cost>
  <Section1RenderedValue>1000.00</Section1RenderedValue>
  <Section2Cost>200.00</Section2Cost>
  <Section2RenderedValue>2000.00</Section2RenderedValue>
  <Section3Cost>300.00</Section3Cost>
  <Section3RenderedValue>3000.00</Section3RenderedValue>
  <Section4Cost>400.00</Section4Cost>
  <Section4RenderedValue>4000.00</Section4RenderedValue>
</root>

answered Jan 8, 2016 at 18:52

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

1 Comment

Fuzzy Over a year ago

This should dynamically create each element, with names based on the values within the SectionName column.

Collectives™ on Stack Overflow

T-SQL: How to select rows as XML element

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related