Generating XML in specific format from SQL query

Question

I need to generate XML from following table

    DECLARE @tempXML AS TABLE
    (
        ID INT IDENTITY(1,1),
        Field VARCHAR(10),
        VALUE VARCHAR(20),
        LEVEL INT
    )

    INSERT INTO @tempXML
    VALUES
        ('FirstName','FN',2),
        ('LastName','LN',2),
        ('Address','testaddress',1),
        ('City','testcity',1)

The XML format is as shown below

<XmlFormat version="1.0">
  <address>
    <field id="Address">testaddress</field>
    <field id="City">testcity</field>   
    <borrower>
      <field id="FirstName">FN</field>
      <field id="LastName">LN</field>      
    </borrower>
  </address>
</XmlFormat>

I have tried the following query but not getting the output in desired xml format

SELECT 
    field AS 'field/@id',   
    value AS 'field/value'
    FROM @tempXML
    FOR     
    XML PATH('borrower'), ELEMENTS, ROOT('address')

The output was

<address>
  <borrower>
    <field id="FirstName">
      <value>FN</value>
    </field>
  </borrower>
  <borrower>
    <field id="LastName">
      <value>LN</value>
    </field>
  </borrower>
  <borrower>
    <field id="Address">
      <value>testaddress</value>
    </field>
  </borrower>
  <borrower>
    <field id="City">
      <value>testcity</value>
    </field>
  </borrower>
</address>

My main problem is to handle the Level values (1 and 2 in the level column of table) and displaying in the required format of XML. If there are any additional entries in table with level values 1 and 2 also should be handled.(eg: ('street','teststreet',1) or ('MidName','MN',2) should come in the correct section of XML).

Please help

I don't like the XML features of SQL Server. I prefer to query database from c# and the use XML Linq in c# to generate the XML. — jdweng
– jdweng, Commented May 25, 2022 at 11:37
@jdweng can you provide the c# version of same solution? i was looking for that — A_developer
– A_developer, Commented May 25, 2022 at 18:16

Yitzhak Khabinsky · Accepted Answer · 2022-05-25 20:48:21Z

Please try the following solution.

It is using XQuery and its FLWOR expression.

Simple and easy, almost visually crafting XML process, no guess work.

The desired output XML is composed in two steps:

Creating raw XML via FOR XML PATH('r'), TYPE, ROOT('root')
Composing fine-tuned final XML via FLWOR expression.

SQL

-- DDL and sample data population, start
DECLARE @tbl AS TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Field VARCHAR(10),
    VALUE VARCHAR(20),
    LEVEL INT
);
INSERT INTO @tbl VALUES
    ('FirstName','FN',2),
    ('LastName','LN',2),
    ('Address','testaddress',1),
    ('City','testcity',1);
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<XmlFormat version="1.0">
    <address>
        {
            for $x in /root/r[LEVEL="1"]
            return  <field id="{data($x/Field)}">{data($x/VALUE)}</field>
        }
        <borrower>
        {
            for $x in /root/r[LEVEL="2"]
            return  <field id="{data($x/Field)}">{data($x/VALUE)}</field>
        }
        </borrower>
    </address>
</XmlFormat>');

Output

<XmlFormat version="1.0">
  <address>
    <field id="Address">testaddress</field>
    <field id="City">testcity</field>
    <borrower>
      <field id="FirstName">FN</field>
      <field id="LastName">LN</field>
    </borrower>
  </address>
</XmlFormat>

verticalPacked · Accepted Answer · 2022-05-25 07:55:37Z

1

One solution would be to use subqueries for each "Level". This example assumes, that your table does not contain two different adresses, because I have not seen a way to group your attributes.

SELECT 
    (
        SELECT 
            field AS 'field/@id',   
            value AS 'field'
        FROM @tempXML
        WHERE LEVEL = 1
        FOR XML PATH(''), TYPE
    )
    ,(
        SELECT 
            field AS 'field/@id',   
            value AS 'field'
        FROM @tempXML
        WHERE LEVEL = 2
        FOR XML PATH(''), Root('borrower'), TYPE        
    )
    FOR XML PATH(''), Root('address'), TYPE

edited May 25, 2022 at 7:55

answered May 25, 2022 at 7:47

verticalPacked

213 bronze badges

Comments

jdweng · Accepted Answer · 2022-05-25 22:08:50Z

Here is the c# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.IO;

namespace ConsoleApplication23
{

    class Program
    {

        static void Main(string[] args)
        {
            XElement address = new XElement("adress");

            XElement borrower = null;

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "FirstName"),
                    new XElement("value", "FN")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "LastName"),
                    new XElement("value", "LN")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "Address"),
                    new XElement("value", "testaddress")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "City"),
                    new XElement("value", "testcity")
                })

            });
            address.Add(new XElement("borrower", borrower));



        }
    }
 
}

Collectives™ on Stack Overflow

Generating XML in specific format from SQL query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related