Pivot Data for XML Export in T-SQL

Question

I have a table in my database that looks like this:

@attribute       element       data
--------------------------------------
attribute1       element1      data1
attribute2       element2      data2

I want to output XML that looks like this:

<element1 attribute="attribute1">data1</element1>
<element2 attribute="attribute2">data2</element2>

There are about 30 ~~attribute~~ element names, sorry, and they're somewhat dynamic. I'm not all that practiced or knowledgeable in SQL. Is there a straightforward solution? Barring that, is there a "best-practice" solution? If I use dynamic SQL, what measures should I take to catch/prevent potential errors?

JamieD77 · Accepted Answer · 2016-01-25 22:23:09Z

2

doesn't really form a valid xml since you're not specifying a root in your sample output but you can do something like this.

SELECT  CAST('<' + element + ' attribute="' + attribute + '">' + data + '</' + element + '>' AS XML)
FROM    Table1
FOR     XML PATH('')

answered Jan 25, 2016 at 22:23

JamieD77

14k1 gold badge19 silver badges27 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Great answer, +1 from my side... You might add ,ROOT('root') after the FOR XML PATH('') to get a valid XML back.

Chip Potato Over a year ago

Thank you, this was exactly what I needed! I knew there had to be something more elegant than dynamic SQL...

Chris Kessler · Accepted Answer · 2016-01-25 22:56:13Z

0

By adding FOR XML RAW at the end of any query will produce XML close to what you are looking for. Also, you can specify element name by adding it at the end, such as FOR XML RAW ('ELEMENT_PARENT'), or a root name, FOR XML RAW ('ELEMENT_PARENT'), ROOT ('Example') .

As for as dynamic elements (or attributes before they are XML-ized) and error catching, you can employ any of standard bag of tricks offered by sql.

FWIW, I found this site that walks through several different examples with increasing complexity on ways to output XML data from a SQL script.

https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

answered Jan 25, 2016 at 22:56

Chris Kessler

395 bronze badges

1 Comment

Chip Potato Over a year ago

Why should I prefer FOR XML PATH() ?

Collectives™ on Stack Overflow

Pivot Data for XML Export in T-SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related