将XML转换为SQL Server 2005表

Question

我想知道如何读取XML数据并将其转换为T-SQL中的表？

例如：

<t1>
<t2>
<val>Opel</val>
<t3>Merriva</t3>
<t3>Zafira</t3>
</t2>
<t2>
<val>Fiat</val>
<t3>Albea</t3>
</t2>
</t1>

收件人：

表1:

id      value
----------------
1        Opel
2        Fiat

表2:

id      id_Table1       value
-----------------------------------
1          1            Merriva
2          1            Zafira
3          2            Albea

Answer 1

我没有一个活动的SQL Server 2005来测试这个(天哪，现在是2018…！！(，但我认为，这个查询也可以在这样的古老版本中工作：

DECLARE @xml XML=
N'<t1>
<t2>
<val>Opel</val>
<t3>Merriva</t3>
<t3>Zafira</t3>
</t2>
<t2>
<val>Fiat</val>
<t3>Albea</t3>
</t2>
</t1>';
--The CTE will return the CarName with a running index together with the related data as XML-node
WITH Cars AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS t2_index
,t2.value(N'(val/text())[1]',N'nvarchar(100)') AS t2_val
,t2.query(N't3') AS t3_nodes
FROM @xml.nodes(N'/t1/t2') A(t2)
)
--This part will append all related data with a running number for the related type data
SELECT Cars.* 
,ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS CarValueIndex
,t3.value(N'text()[1]',N'nvarchar(100)') AS CarValue
INTO #tmpCars --write the result into a temp table
FROM Cars
OUTER APPLY t3_nodes.nodes(N't3') A(t3);
--All data de-normalized
SELECT * FROM #tmpCars;
--This query will bring back the parent rows
SELECT t2_index AS CarID
,t2_val AS CarName
FROM #tmpCars
GROUP BY t2_index,t2_val;
--And this query will return the related child data
SELECT CarValueIndex AS CarTypeID
,t2_index AS fk_CarID
,CarValue AS CarType
FROM #tmpCars;
GO
DROP TABLE #tmpCars;

如果有任何机会转移到更现代的SQL Server，你真的应该这样做…

更新

根据这个链接，.value()和.nodes()功能是在v2008中引入的。但我有一个黑暗的记忆，它在2005年就已经开始工作了，可能会和一些服务包在一起。。。试试看。