如何将XML数据转换为SQL Server表

Question

我有一个像这样的 XML 数据：

<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>

我该如何将XML数据转换为如下表格：

    AT         |lifespan|direction |ID |Value 
---------------------------------------------
Wed Oct 15 2014|2279ms  |in        |0  |0800
Wed Oct 15 2014|2279ms  |in        |3  |980000
Wed Oct 15 2014|2279ms  |in        |11 |000852

- Eddy Yakup

---

请查看此问题。 - Ram

你也可以考虑创建一个XSLT来将数据转换为SQL脚本。 - user2033671

Answer 1

这比@Nick的回答要简单得多，因为它只需要一个.nodes()调用，而不是三个嵌套的调用...

DECLARE @input XML = '<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>'

SELECT
    At = xc.value('../../../@at', 'varchar(50)'),
    Lifespan = xc.value('../../../@lifespan', 'varchar(25)'),
    Direction = xc.value('../@direction', 'varchar(10)'),
    ID = XC.value('@id', 'int'),
    Value = xc.value('@value', 'varchar(25)')
FROM
    @Input.nodes('/root/log/receive/isomsg/field') AS XT(XC)

调用@Input.nodes实际上返回一个“虚拟”XML片段表，代表每个<field> XML元素。通过使用..，我们还可以在原始文档中向上导航XML层次结构，以访问<isomsg>和<log>元素，并从中获取属性值。

- marc_s

---

不错！比我的整洁多了。我猜这个适用于从表中选择数据（而不是带有 XML 数据的变量）？我之所以问，是因为我最初也有类似的东西，但在让它工作时遇到了麻烦。我从顶层 /root/log 开始节点，可能是问题所在。 - Nick H.

哇，太简单了。非常感谢marc_s，非常感谢Nick。 - Eddy Yakup

Answer 2

这是我实现它的方法（尽管有多种方法）。

WITH xmlData AS (
SELECT CAST('<root>
 <log realm="ABC" at="Wed Oct 15 00:00:02 2014.211" lifespan="2279ms">
  <receive>
    <isomsg direction="IN">
      <header>6000911384</header>
      <field id="0" value="0800"/>
      <field id="3" value="980000"/>
      <field id="11" value="000852"/>
    </isomsg>
  </receive>
</log>
</root>' AS XML) as xmlData)
SELECT xmlData, logs.x.value('(@at)[1]','varchar(50)') as 'Element1', logs.x.value('(@lifespan)[1]','varchar(50)') as 'Element2', ismsgs.logs.value('(@direction)[1]','varchar(50)') as 'Element3', fields.ismsgs.value('(@value)[1]','varchar(50)') as 'Element4'
FROM xmlData x
CROSS APPLY xmlData.nodes('root/log') logs(x)   
CROSS APPLY xmlData.nodes('root/log/receive/isomsg') ismsgs(logs)
CROSS APPLY xmlData.nodes('root/log/receive/isomsg/field') fields(ismsgs)

- Nick H.