主页 / coding / 问题 / 79168239
Accepted
Leah
Asked: 2024-11-08 06:19:30 +0800 CST

从 SQL 表创建 XML 表，其中 SQL 列标题是第一行？

  • 772

我正在尝试从 SQL 表中选择值作为具有特定格式的 XML 表。

使用下表...

SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

SELECT *
FROM #t

DROP TABLE #t

我需要一个看起来像这样的 XML 输出......

<Table>
  <Row>
    <Column>Id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
</Table>

我能够使用 UNION 和 XML PATH 获得与此匹配的输出...

SELECT t1.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) t1(Id, FirstName, LastName);

WITH t2(xmlTable) AS (
        SELECT t3.Id 'Column'
              ,''
              ,t3.FirstName 'Column'
              ,''
              ,t3.LastName 'Column'
        FROM (
            SELECT *
            FROM (VALUES ('Id', 'FirstName', 'LastName')) t4(Id, FirstName, LastName)
            UNION ALL
            SELECT CONVERT(varchar(15), #t.Id)
                  ,CONVERT(varchar(15), #t.FirstName)
                  ,CONVERT(varchar(15), #t.LastName)
            FROM #t) t3
        FOR XML PATH ('Row'), ROOT('Table'))

SELECT t2.xmlTable
FROM t2

DROP TABLE #t

但这似乎非常麻烦。有没有更优雅的解决方案？具体来说，我可以不使用 UNION 来做到这一点，也许只使用 XML PATH？

sql

2 个回答

  1. Best Answer

    请尝试以下基于 SQL Server 本机 XQuery 的解决方案。

    无论表有多少列，它都是通用的。

    列名没有在任何地方硬编码。

    值得注意的一点：

    • 首先，内部SELECT在后台组成原始 XML（请参见下文）。
    • 第二种方法是XQuery.query()方法是使用 XQuery FLWOR 表达式“直观地”组合所需的输出。
    • XPath 表达式/root/r[1]/*将 FLWOR 限制为仅进行一次迭代，以通过调用 XQuery 函数动态检索列名local-name() 。

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, FirstName VARCHAR(30), LastName VARCHAR(30));
INSERT INTO @tbl (FirstName, LastName) VALUES
('John', 'Doe'),
('Mary', 'Pollack');
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<Table><Row>{
    for $x in /root/r[1]/*
    return <Column>{local-name($x)}</Column>
}</Row>{
    for $x in /root/r
    return <Row>{
        for $y in $x/*
        return <Column>{data($y)}</Column>}</Row>
}
</Table>');

    原始 XML

    <root>
  <r>
    <id>1</id>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </r>
  <r>
    <id>2</id>
    <FirstName>Mary</FirstName>
    <LastName>Pollack</LastName>
  </r>
</root>

    输出

    <Table>
  <Row>
    <Column>id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
  <Row>
    <Column>2</Column>
    <Column>Mary</Column>
    <Column>Pollack</Column>
  </Row>
</Table>
    • 4

  2. 不，如果不使用，就不可能在单级查询中执行此操作UNION ALL。 SQL Server 期望节点值是来自表的数据，而不是对象或列名（通常应定义为静态）。

    您可以使用@YitzakKabinsky 的出色答案，但另一个完全动态的选项是使用FOR JSON并OPENJSON聚合并重新查询数据。

    SELECT
  (
    SELECT j2.[key] AS [Column]
    FROM (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    FOR XML PATH(''), TYPE
  )
FROM (SELECT TOP (1) * FROM #t) t

UNION ALL

SELECT
  (
    SELECT j2.[value] AS [Column]
    FROM (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    FOR XML PATH(''), TYPE
  )
FROM #t t
FOR XML PATH('Row'), ROOT('Table'), TYPE;

    db<>小提琴

    • 1

相关问题