XML generate from SQL Query with perfect XML structure

Question

MY SQl "employee" Table Look Like

+-------+---------+--------+----------+
| Empid | Empname | Salary | Location |
+-------+---------+--------+----------+
|     1 | Arul    |    100 | Chennai  |
+-------+---------+--------+----------+

XML Generate from SQl Query:

select * from employee for xml path, root('root')

from this Sql Query I'm Getting My XML Files as given below

<root>
  <employee>
    <Empid>1</Empid>
    <Empname>Arul</Empname>
    <Salary>100</Salary>
    <Location>Chennai</Location>
  </employee>
</root>

But My Expected Output XML from SQL query as

<root>
<column>Empid</column>
<value>1</value>
<column>Empname</column>
<value>Arul</value>
</root>

The XML you're getting now is the much better one - clearly structured, self-documenting ...... — marc_s
– marc_s, Commented Nov 30, 2018 at 6:01
But My Expected Output XML from SQL query as <root> <column>Empid</column> <value>1</value> <column>Empname</column> <value>Arul</value> </root> — abirami
– abirami, Commented Nov 30, 2018 at 6:29
@abirami that doesn't make it a good format. How would anyone find the Empname value int this? What would the XPath query look like? How would a .NET client deserialize this? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 30, 2018 at 12:01

Gottfried Lesigang · Accepted Answer · 2018-11-30 12:37:07Z

2

As you were told already, the needed output format is really bad and erronous. Nevertheless this can be done quite easily:

DECLARE @mockup TABLE(Empid INT,Empname VARCHAR(100),Salary DECIMAL(10,4),[Location] VARCHAR(100));
INSERT INTO @mockup VALUES(1,'Arul',100,'Chennai')
                         ,(2,'One',200,'More');

SELECT 'Empid' AS [Column]
      ,EmpId AS [Value]
      ,'Empname' AS [Column]
      ,Empname AS [Value]
      -- follow this pattern...
FROM @mockup t
FOR XML PATH('employee'),ROOT('root');

The result

<root>
  <employee>
    <Column>Empid</Column>
    <Value>1</Value>
    <Column>Empname</Column>
    <Value>Arul</Value>
  </employee>
  <employee>
    <Column>Empid</Column>
    <Value>2</Value>
    <Column>Empname</Column>
    <Value>One</Value>
  </employee>
</root>

But - by any chance - you should try to change this format. This is awful to query and will be your private headache for sure...

Some better suggestions:

<Employee>
  <Column name="EmpId" value="1" />
  <Column name="Empname" value="Arul" />
</Employee>

or

<employee id="1" name="Arul" />

or

<employee>
  <Id>1</Id>
  <Name>Arul</Name>
</employee>

or (if you really, really want to stick with this), at least a column index like here

<root>
  <employee>
    <Column inx="1">Empid</Column>
    <Value inx="1">1</Value>
    <Column inx="2">Empname</Column>
    <Value inx="2">Arul</Value>
  </employee>
  <employee>
    <Column inx="1">Empid</Column>
    <Value inx="1">2</Value>
    <Column inx="2">Empname</Column>
    <Value inx="2">One</Value>
  </employee>
</root>

The query for the last one is this

SELECT 1 AS [Column/@inx]
      ,'Empid' AS [Column]
      ,1 AS [Value/@inx]
      ,EmpId AS [Value]
      ,2 AS [Column/@inx]
      ,'Empname' AS [Column]
      ,2 AS [Value/@inx]
      ,Empname AS [Value]
      -- follow this pattern...
FROM @mockup t
FOR XML PATH('employee'),ROOT('root');

edited Nov 30, 2018 at 12:37

answered Nov 30, 2018 at 11:58

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

abirami Over a year ago

It's Worked for me.SELECT 'Empid' AS [Column] ,EmpId AS [Value] ,'Empname' AS [Column] ,Empname AS [Value] -- follow this pattern... FROM @mockup t .. here i need to mention my table column name. if my table contains 60 fields i need to mention my all columns here.is there is any simplest way for the best solution.

Gottfried Lesigang Over a year ago

@abirami, No, there is not "simplest way", you'll have to type this in... Well, you might create the statement dynamically, but I doubt this will be simpler... However, if this solves your issue, it would be kind to tick the acceptance check. Happy Coding!

Collectives™ on Stack Overflow

XML generate from SQL Query with perfect XML structure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related