CODEWITHSUNDEEP
sql-serverxmlt-sqlfor-xml
LP13
LP13

Reputation: 34089

T-SQL: How to select rows as XML element

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

Upvotes: 2

Views: 2703

Answers (3)

Fuzzy
Fuzzy

Reputation: 3810

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>

Upvotes: 0

LP13
LP13

Reputation: 34089

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')

Upvotes: 0

Paul Hendrickson
Paul Hendrickson

Reputation: 46

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>

Upvotes: 1

Related Questions