Generating XML in specific format from SQL query-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Please try the following solution.

It is using XQuery and its FLWOR expression.

Simple and easy, almost visually, 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>