SQL Server : FOR XML specific output

Question

I have been trying (with no success yet) to produce the following XML output

<table>
    <row>
        <column name="column_name">[value]</column>
        <column name="column_name">[value]</column>
    </row>
    <row>
        <column name="column_name">[value]</column>
        <column name="column_name">[value]</column>
    </row>
</table>

Can anyone suggest a route that will allow me to achieve this output?

Thank you in advance for any help you can provide.

Just google "SQL SERVER SELECT FOR XML". There's tons of stuff on this including tutorials and many answers on this site. If you have a specific question, please let us know. — RBarryYoung
– RBarryYoung, Commented Nov 11, 2015 at 22:44
Could you give some examples of SQL statements you've tried so far? StackOverflow is best when you have a specific question or issue that you are trying to resolve based on the effort you've put in so far. — danjuggler
– danjuggler, Commented Nov 12, 2015 at 1:05

Gottfried Lesigang · Accepted Answer · 2016-04-19 16:13:20Z

1

Don't know if this is still of interest, but the code was this:

(The trick is the "empty node" between the two column declarations...)

DECLARE @tbl TABLE(col1 INT, col2 VARCHAR(100));
INSERT INTO @tbl VALUES(1,'test1'),(2,'test2');

SELECT 'col1' AS [column/@name]
      ,col1 AS [column]

      ,'' --this is needed to allow the same name twice...

      ,'col2' AS [column/@name]
      ,col2 AS [column]
FROM @tbl
FOR XML PATH('row'),ROOT('table');

/*
<table>
  <row>
    <column name="col1">1</column>
    <column name="col2">test1</column>
  </row>
  <row>
    <column name="col1">2</column>
    <column name="col2">test2</column>
  </row>
</table>
*/

answered Apr 19, 2016 at 16:13

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

SQL Server : FOR XML specific output

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