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>

CodePudding user response：

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>

CodePudding user response：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VENDORS](
[VENDOR_ID] [int] IDENTITY(1,1) NOT NULL,
[VENDORS_Name] [varchar](100) NULL,
CONSTRAINT [PK_VENDORS] PRIMARY KEY CLUSTERED 
(
[VENDOR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATEfunction [dbo].[GetColumnInfo]  
(  
 @TABLE_SCHEMA nchar(100),  
 @TABLE_NAME nvarchar(100),  
 @COLUMN_NAME varchar(100)  
)  
 returns nvarchar(150)  
as  
begin 
DECLARE @_DataType Varchar(150)
DECLARE @_RETURNType Varchar(150)
SET @_DataType = (SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_SCHEMA = @TABLE_SCHEMA AND
     TABLE_NAME   = @TABLE_NAME AND 
     COLUMN_NAME  = @COLUMN_NAME
)

IF @_DataType = 'int'
BEGIN
   SET @_RETURNType= 'NUMBER'
END

IF @_DataType = 'varchar'
BEGIN
   SET @_RETURNType= 'STRING'
END

RETURN @_RETURNType
end  

 SELECT
'VENDER_ID' as 'Key',
nodes.VENDOR_ID as 'value',
dbo.GetColumnInfo('dbo','VENDORS','VENDOR_ID')
as logicalType,
'VENDERS_Name' as 'key',
nodes.VENDORS_Name as 'value',
dbo.GetColumnInfo('dbo','VENDORS','VENDORs_Name')
as logicalType
FROM
VENDORS as nodes
FOR XML path('attributes'), ROOT('nodes')