Generating XML in specific format from SQL query

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

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>