前端之家

  1. 首页
  2. 问答

XML分解动态SQL

• 问答

我使用the instructions from the 1st answer here试图在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);

任何帮助将不胜感激。

wangjie5555 回答：XML分解动态SQL

这里是DDL和 XQuery ，用于将表中的XML切碎。无需任何动态SQL。 MS SQL Server支持 XQuery 1.0标准的子集。 微软需要实现XQuery 3.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 |
+------+--------------+-----------+--------+-------+
,

这里是一个动态的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,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);
sqlsql-serverxmlxquery
本文链接：https://www.f2er.com/3155749.html

大家都在问