xml - 在 SQL Server 中使用 FOR XML PATH 从 table 到 XML 需要奇数形式的 XML

我需要找到一种方法来创建存储过程或查询,以便从下面列出的数据中开发 XML。

SQL Server table values

Table 名称:VENDORS

  1. 第 1 列

    名称:VENDOR_ID

    VALUE:201 数据类型:编号

  2. 第 2 列

    名称:VENDOR_NAME

    VALUE:ABC CORP 数据类型:字符串

结果 XML 需要:

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

回答1

请尝试以下解决方案。

没有提供最小的可重现示例。所以,我是从臀部射击。

格式良好的 XML 应该有一个根元素。这就是组合 XML 具有 <root> 根元素的原因。

<node> 元素对应于数据库 table 行。

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

输出

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

回答2

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

相似文章

python - python 猜数字游戏,猜多

我正在学习编程1并且正在做1-100的猜数游戏,我需要一个命令,如果我猜超过100,程序会看到它并告诉我,我不知道我应该如何得到这个答案。我已经猜到我认为该命令会是什么样子以及它最远的地方,但如果我完...

最新文章