XML切碎动态SQL

Question

使用第一个答案中的说明，我试图在SQL Server 2012表中分解一些XML，如下所示：

表格A

+------+--------+
|  ID  | ColXML |
+------+--------+
| 0001 | <xml1> |
| 0002 | <xml2> |
| ...  | ...    |
+------+--------+

xml1看起来像这样：

<Attributes>
<Attribute name="address1">301 Main St</Attribute>
<Attribute name="city">Austin</Attribute>
</Attributes>

xml2看起来像这样：

<Attributes>
<Attribute name="address1">501 State St</Attribute>
<Attribute name="address2">Suite 301</Attribute>
<Attribute name="state">Texas</Attribute>
</Attributes>

任何给定行中都有不同数量的属性。

我正试图将它展开为一个关系表，它看起来像这样：

+------+--------------+-----------+--------+-------+
|  ID  |   address1   | address2  |  city  | state |
+------+--------------+-----------+--------+-------+
| 0001 | 301 Main St  | NULL      | Austin | NULL  |
| 0002 | 501 State St | Suite 301 | NULL   | Texas |
+------+--------------+-----------+--------+-------+

以下是我尝试过的返回#T:表中0行的代码

select dense_rank() over(order by ID, I.N) as ID,
F.N.value('(*:Name/text())[1]', 'varchar(max)') as Name,
F.N.value('(*:Values/text())[1]', 'varchar(max)') as Value
into #T
from TableA as T
cross apply T.Attributes.nodes('/ColXML') as I(N)
cross apply I.N.nodes('ColXML') as F(N);
declare @SQL nvarchar(max)
declare @Col nvarchar(max);
select @Col = 
(
select distinct ','+quotename(Name)
from #T
for xml path(''), type
).value('substring(text()[1], 2)', 'nvarchar(max)');
set @SQL = 'select '+@Col+'
from #T
pivot (max(Value) for Name in ('+@Col+')) as P';
exec (@SQL);

如有任何帮助，我们将不胜感激。

Answer 1

这里有一个DDL和XQuery来分解表中的XML。不需要任何动态SQL。MS SQL Server支持XQuery1.0标准的子集。微软需要实现XQuery3.1，使其数据库更加强大。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID VARCHAR(10) PRIMARY KEY, ColXML XML);
INSERT INTO @tbl (ID, ColXML)
VALUES
('0001', N'<Attributes>
<Attribute name="address1">301 Main St</Attribute>
<Attribute name="city">Austin</Attribute>
</Attributes>'),
('0002', N'<Attributes>
<Attribute name="address1">501 State St</Attribute>
<Attribute name="address2">Suite 301</Attribute>
<Attribute name="state">Texas</Attribute>
</Attributes>');
-- DDL and sample data population, end
SELECT ID
, col.value('(Attribute[@name="address1"]/text())[1]','VARCHAR(30)') AS [address1]
, col.value('(Attribute[@name="address2"]/text())[1]','VARCHAR(30)') AS [address2]
, col.value('(Attribute[@name="city"]/text())[1]','VARCHAR(30)') AS [city]
, col.value('(Attribute[@name="state"]/text())[1]','VARCHAR(30)') AS [state]
FROM @tbl AS tbl
CROSS APPLY tbl.ColXML.nodes('/Attributes') AS tab(col);

输出

+------+--------------+-----------+--------+-------+
|  ID  |   address1   | address2  |  city  | state |
+------+--------------+-----------+--------+-------+
| 0001 | 301 Main St  | NULL      | Austin | NULL  |
| 0002 | 501 State St | Suite 301 | NULL   | Texas |
+------+--------------+-----------+--------+-------+

Answer 2

这里是一个基于我之前的工作建议的动态SQL解决方案。它发出相同的输出。

SQL

USE tempdb;
GO
DROP TABLE IF EXISTS #tbl;
-- DDL and sample data population, start
CREATE TABLE #tbl (ID VARCHAR(10) PRIMARY KEY, ColXML XML);
INSERT INTO #tbl (ID, ColXML)
VALUES
('0001', N'<Attributes>
<Attribute name="address1">301 Main St</Attribute>
<Attribute name="city">Austin</Attribute>
</Attributes>'),
('0002', N'<Attributes>
<Attribute name="address1">501 State St</Attribute>
<Attribute name="address2">Suite 301</Attribute>
<Attribute name="state">Texas</Attribute>
</Attributes>');
-- DDL and sample data population, end
DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
, @sql VARCHAR(MAX) = 'SELECT ID ';
SET @sql += @CrLf;
;WITH rs AS
(
SELECT DISTINCT col.value('@name','VARCHAR(30)') AS colName
FROM #tbl AS tbl
CROSS APPLY tbl.ColXML.nodes('/Attributes/Attribute') AS tab(col)
)
SELECT @sql += ', col.value(''(Attribute[@name="' + colName + '"]/text())[1]'',''VARCHAR(30)'') AS [' + colName + ']' + @CrLf 
FROM rs;
SET @sql += 'FROM #tbl AS tbl
CROSS APPLY tbl.ColXML.nodes(''/Attributes'') AS tab(col);';
PRINT @sql;
EXEC(@sql);