将XML数据导入SQL Server表

Question

我在网上查找了使用T-SQL将XML数据转换为表格的方法。但是我只看到了部分答案。我的XML格式略有不同。请问是否有人能帮助我解决以下问题。

DECLARE @XmlData XML

SELECT @XmlData = '<header>
  <data>
    <numbersData>
      <numbers>
        <number>1</number>
        <number>2</number>
      </numbers>
    </numbersData>
  </data>
  <data>
    <numbersData>
      <numbers>
        <number>3</number>
        <number>4</number>
      </numbers>
    </numbersData>
  </data>
</header>'

查询：

SELECT 
    Numbers.Data.value('(number)[1]', 'VARCHAR(100)') AS Num
FROM
    @XmlData.nodes('/header/data/numbersData/numbers') Numbers(Data)

这个的结果是：

Num
----
1
3

注意，它只返回1和3。2和4都缺失了。

不幸的是，这个文件来自客户端，他们不会更改格式。我正在尝试获得以下结果

Num
---
1
2
3
4

- gsva

---

你从哪个<data>节点获取数字并不重要。 - Shnugo

Answer 1

为什么要将那些明显是数字的值转换为VARCHAR（100）？这毫无意义... 无论如何 - 尝试使用此SELECT语句：

SELECT 
    Data.value('.', 'INT') AS Num
FROM
    @XmlData.nodes('/header/data/numbersData/numbers/number') Numbers(Data)

XPath基本上会一直“向下钻取”，直到所有<number> XML元素，因此获取了所有这些元素的完整列表，并将它们的值转换为INT - 这样就输出了所需的四个数字。

- marc_s

---

是的，在我的例子中它们是数字。但在某些情况下它们是字母数字混合的。这个例子被简化了，以便让每个人都能理解。谢谢你的帮助！ - gsva

Answer 2

如果你只需要数字，那么你已经得到了答案。以下答案还会告诉你数字来自哪个节点：

WITH theData AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS DataIndex
          ,d.query('.') DataNode
    FROM @XmlData.nodes('/header/data') A(d)
)
SELECT d.DataIndex
      ,n.value('text()[1]','int') AS TheNumber
FROM theData d
CROSS APPLY d.DataNode.nodes('data/numbersData/numbers/number') A(n);

结果

DataIndex   TheNumber
1           1
1           2
2           3
2           4

- Shnugo

---

谢谢您的回复。是的，顺序并不重要。 - gsva