将SQL表转换为XML，以列作为父节点

Question

我正在尝试将一个表格转换成XML结构，我希望表格中的一列代表父节点，另一列代表子节点。

我已经完成了部分工作，但是我没有完整的解决方案。我需要将TABLE_NAME列转换为xml父节点，将COLUMN_NAME列转换为子节点。如果我执行以下操作，我会得到嵌套，但我也会得到多个父节点。

select
 TABLE_NAME AS 'tn',
 COLUMN_NAME AS 'tn/cn'
from (
 select 'TABLE_A' AS TABLE_NAME, 'COLUMN_1' AS COLUMN_NAME
 UNION ALL
 select 'TABLE_A' AS TABLE_NAME, 'COLUMN_2' AS COLUMN_NAME
 UNION ALL
 select 'TABLE_B' AS TABLE_NAME, 'COLUMN_1' AS COLUMN_NAME
 UNION ALL
 select 'TABLE_B' AS TABLE_NAME, 'COLUMN_2' AS COLUMN_NAME
) x
for xml path(''), ROOT('datatable')

输出>>>

<datatable>
  <tn>TABLE_A<cn>COLUMN_1</cn></tn>
  <tn>TABLE_A<cn>COLUMN_2</cn></tn>
  <tn>TABLE_B<cn>COLUMN_1</cn></tn>
  <tn>TABLE_B<cn>COLUMN_2</cn></tn>
</datatable>

期望输出 >>>

<datatable>
  <TABLE_A>
   <cn>COLUMN_1</cn>
   <cn>COLUMN_2</cn>
  </TABLE_A>
  <TABLE_B>
    <cn>COLUMN_1</cn>
    <cn>COLUMN_2</cn>
  </TABLE_B>
</datatable>

这是可能的，还是我在做梦？是否可以不使用XML EXPLICIT实现，或者这种情况需要EXPLICIT？

我一直尝试的另一种可能性是将xml填充然后应用xquery，但是还没有成功。

谢谢，

Gary

- sqlconsumer.net

---

@Gary，你正在使用哪种SQL方言（Oracle、SQLServer、MySQL等）？其中一些方言有特定于处理XML的扩展。 - user359040

嗨马克，我正在使用M$ SQL Server 2008 R2标准版。 - sqlconsumer.net

你考虑过先将数据导出为 XML 格式，然后再执行 XSL 转换吗？ - Dave Jarvis

Answer 1

很遗憾，您想要做的事情是不可能的。有两个主要问题（如果您在任何一个问题上有余地，则可能会有解决方案）。

首先，SQL中的所有XML选项（甚至使用EXPLICIT）都不允许动态节点命名。您只能使用属性<tn id="TABLE_A" />或值<tn>TABLE_A</tn>，但是除非您硬编码它，否则无法获取<TABLE_A>。

XML类型确实允许嵌套/子查询。

SELECT V1.tbname
,(SELECT V2.colname FROM testtable V2 
  WHERE V1.tbname = V2.tbname FOR XML PATH(''), ELEMENTS, TYPE)
FROM testtable V1
FOR XML AUTO, ROOT('datatable')

您的第二个问题源于数据非规范化。无法获取唯一表列表（您不能在上述情况下使用DISTINCT，因为SQL无法比较XML类型）。这限制了您在单个“传递”（语句）中可以执行的操作。

如果您愿意使用临时表（或表变量），则可以选择一个不同的表名称列表，然后像给出的示例那样将其与列名称连接（首先运行以下内容，并将外部from替换为@tblist）。

DECLARE @tblist TABLE (tbname varchar(20))
INSERT INTO @tblist SELECT DISTINCT tbname FROM testtable  

它返回以下内容：

<datatable>
  <V1 tbname="TBA">
    <colname>COL 1</colname>
    <colname>COL 2</colname>
  </V1>
  <V1 tbname="TBB">
    <colname>COL 1</colname>
    <colname>COL 2</colname>
  </V1>
</datatable>

你还需要愿意将表名节点设置为属性（之后可以使用GREP或简单替换将节点设为值），这将接近或完全符合你要求的格式。

很抱歉，这可能不是你想听到的。但只需几个简单的步骤即可完成。只是不能直接从SQL Server中以单个语句完成。

- ktharsis

Answer 2

正如其他人所提到的，FOR XML 不允许您动态地命名节点。节点名称必须在查询本身被编译时成为常量。您可以通过动态SQL来解决这个问题，但是这样做会使代码变得越来越难以阅读。

另一种方法是手动生成表名节点并转换为XML：

设置：

CREATE TABLE a (table_name VARCHAR(20), column_name VARCHAR(20)
INSERT INTO a VALUES ('TABLE_A', 'COLUMN_1')
INSERT INTO a VALUES ('TABLE_A', 'COLUMN_2')
INSERT INTO a VALUES ('TABLE_B', 'COLUMN_1')
INSERT INTO a VALUES ('TABLE_B', 'COLUMN_2')

执行：

SELECT CAST(
      '<' + table_name + '>'
    + (SELECT c.column_name as 'CN'
         FROM a c
        WHERE c.table_name = p.table_name
       FOR XML PATH('')) 
    + '</' + table_name + '>'
    AS XML)
  FROM a p
GROUP BY p.table_name
FOR XML PATH(''), ROOT('datatable')

生成：

<datatable>
  <TABLE_A>
    <CN>COLUMN_1</CN>
    <CN>COLUMN_2</CN>
  </TABLE_A>
  <TABLE_B>
    <CN>COLUMN_1</CN>
    <CN>COLUMN_2</CN>
  </TABLE_B>
</datatable>

- Jeff Wight

Answer 3

有可能需要使用路径来命名列。像这样：'parent\child'。

试试这个：

select
( 
 select * from (
     select 'COLUMN_1' 'cn'
     UNION ALL
     select 'COLUMN_2' 'cn' 
 ) as t
 for xml path(''), root('TABLE_A'), type
)
,( 
 select * from (
     select 'COLUMN_1' 'cn'
     UNION ALL
     select 'COLUMN_2' 'cn' 
 ) as t
 for xml path(''), root('TABLE_B'), type
)
for xml path(''), ROOT('datatable')

- Denis Valeev

---

抱歉，我需要一个生产解决方案，可以处理大型表格输入，而无需像root('TABLE_A')这样硬编码的东西。 - sqlconsumer.net

我越想越觉得这是做不到的。我希望我的第一列数据代表节点名称，第二列数据代表节点值。 - sqlconsumer.net

在 @sqlconsumer.net 上，您可以构建任何类型的 XML 作为 varchar，然后将其转换为 xml。 - Denis Valeev

我只按照预期的方式编写干净的代码。玩弄大量字符串操作并不是答案。理解FOR XML子句的能力才是目标。 - sqlconsumer.net

Answer 4

请查看这篇关于 SQL 转 XML 的非常详细的文章。希望能对您有所帮助 -

http://www.stylusstudio.com/sqlxml_tutorial.html

- Sachin Shanbhag

---

遗憾地，这与 SQL Server 没有任何关系。 - Denis Valeev