Odd forming of XML needed from a table to XML using FOR XML PATH in SQL Server

Question

I needed to find a way to create either a stored procedure or query to develop the XML from the data listed below.

SQL Server table values

Table name: VENDORS

1. Column #1

   Name: VENDOR_ID
   VALUE: 201 Data_Type: Number

2. Column #2

   Name: VENDOR_NAME
   VALUE: ABC CORP Data_Type: String

Result XML needed:

<node>
    <attribute>
        <key>VENDOR_ID</key>
        <value>20</value>
        <logicalType>Number</logicalType>
    </attribute>
    <attribute>
        <key>VENDOR_NAME</key>
        <value>ABC CORP</value>
        <logicalType>STRING</logicalType>
    </attribute>
</node>

Answer 1

Please try the following solution.

A minimal reproducible example is not provided. So, I am shooting from the hip.

Well-formed XML supposed to have a root element. That's why composed XML has the <root> root element.

And the <node> element is corresponding to the database table row.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (VENDOR_ID INT PRIMARY KEY, VENDOR_NAME VARCHAR(30));
INSERT INTO @tbl (VENDOR_ID, VENDOR_NAME) VALUES
(201, 'ABC CORP'),
(202, 'Greico Chevrolet');
-- DDL and sample data population, end

SELECT 'VENDOR_ID' AS [attribute/key]
    , VENDOR_ID AS [attribute/value]
    , 'Number' AS [attribute/logicalType]
    , ''
    , 'VENDOR_NAME' AS [attribute/key]
    , VENDOR_NAME AS [attribute/value]
    , 'STRING' AS [attribute/logicalType]
FROM @tbl
FOR XML PATH('node'), TYPE, ROOT('root');

Output

<root>
  <node>
    <attribute>
      <key>VENDOR_ID</key>
      <value>201</value>
      <logicalType>Number</logicalType>
    </attribute>
    <attribute>
      <key>VENDOR_NAME</key>
      <value>ABC CORP</value>
      <logicalType>STRING</logicalType>
    </attribute>
  </node>
  <node>
    <attribute>
      <key>VENDOR_ID</key>
      <value>202</value>
      <logicalType>Number</logicalType>
    </attribute>
    <attribute>
      <key>VENDOR_NAME</key>
      <value>Greico Chevrolet</value>
      <logicalType>STRING</logicalType>
    </attribute>
  </node>
</root>