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

Answer 1

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:

1. Creating raw XML via FOR XML PATH('r'), TYPE, ROOT('root')
2. 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>

Answer 2

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

Answer 3

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



        }
    }
 
}