时间戳

首页 > 解决方案 > 为 SQL Server 中没有子节点的每个 XML 节点创建一列

sql - 为 SQL Server 中没有子节点的每个 XML 节点创建一列

问题描述

我正在尝试在 SSMS 中查询一个 xml 文件以创建一个表，该表由作为所有 xml 节点列表的第一行和包含其所有值的下一行组成。xml 数据将具有一个重复节点，该节点将为具有该名称的每个重复节点创建一个新行。这是 xml 数据的示例：

<Tests>
<TestAnswers>
    <Question1>
        <Name>Example</Name>
        <Age>23</Age>
        <Question1B>
            <Title>Singer</Title>
        </Questions1B>
    </Question1>
    <Question2>
        <Car></Car>
        <Model>Model 1</Model>
        <Question2B>
            <Year>1986</Year>
            <Manufactured></Manufactured>
        </Questions2B>
    </Question2>
</TestAnswers>
<TestAnswers>
    <Question1>
        <Name>Santa</Name>
        <Age></Age>
        <Question1B>
            <Title>Writer</Title>
        </Questions1B>
    </Question1>
    <Question2>
        <Car>This car</Car>
        <Model>Model2</Model>
        <Question2B>
            <Year></Year>
            <Manufactured></Manufactured>
        </Questions2B>
    </Question2>
</TestAnswers>
</Tests>

我想在 SQL Server 中创建的表应该类似于：

   |    Name     | Age |    Title    |    Car    |    Model    |   Year   |    Manufactured    |
-------------------------------------------------------------------------------------------------
 1 | Example     | 23  | Singer      |           | Model 1     | 1986     |                    |
 2 | Santa       |     | Writer      | This Car  | Model2      |          |                    |

随着 xml 文件随着更多信息而变得更大，将会创建更多行。我不想为包含子节点的 xml 节点创建列。

如何编写查询来执行此任务？

更新

新的 XML 用例，其中将有两列由重复节点组成：

<Tests>
<TestAnswers>
    <Question1>
        <Name>Example</Name>
        <Age>23</Age>
        <Other>Other Text</Other>
        <Question1B>
            <Title>Singer</Title>
            <Other></Other>
        </Question1B>
    </Question1>
    <Question2>
        <Car></Car>
        <Model>Model 1</Model>
        <Question2B>
            <Year>1986</Year>
            <Manufactured></Manufactured>
        </Question2B>
    </Question2>
</TestAnswers>
<TestAnswers>
    <Question1>
        <Name>Santa</Name>
        <Age></Age>
        <Other></Other>
        <Question1B>
            <Title>Writer</Title>
            <Other>Text</Other>
        </Question1B>
    </Question1>
    <Question2>
        <Car>This car</Car>
        <Model>Model2</Model>
        <Question2B>
            <Year></Year>
            <Manufactured></Manufactured>
        </Question2B>
    </Question2>
</TestAnswers>
</Tests>

标签： sqlsql-serverxmlssms

解决方案

此 XML 是否在您的控制之下？

除了它无效（子元素的结束标签<Question2B>是复数<Questions2B>）这一事实之外，还有严重的设计缺陷......

最明显的是名称编号，例如<Question1>和<Question2>...

如果内容也可能有所不同，请提供更多详细信息。你正在写更多的行，有更多的信息。您的意思是更多相同还是可能还有其他列甚至其他结构？

除此之外，这里还有一些展示原则的一般方法：

DECLARE @xml XML=
N'<Tests>
<TestAnswers>
    <Question1>
        <Name>Example</Name>
        <Age>23</Age>
        <Question1B>
            <Title>Singer</Title>
        </Question1B>
    </Question1>
    <Question2>
        <Car></Car>
        <Model>Model 1</Model>
        <Question2B>
            <Year>1986</Year>
            <Manufactured></Manufactured>
        </Question2B>
    </Question2>
</TestAnswers>
<TestAnswers>
    <Question1>
        <Name>Santa</Name>
        <Age></Age>
        <Question1B>
            <Title>Writer</Title>
        </Question1B>
    </Question1>
    <Question2>
        <Car>This car</Car>
        <Model>Model2</Model>
        <Question2B>
            <Year></Year>
            <Manufactured></Manufactured>
        </Question2B>
    </Question2>
</TestAnswers>
</Tests>';

--这将逐行返回所有答案

SELECT a.query('.')
FROM @xml.nodes('/Tests/TestAnswers') A(a);

--这将逐行返回所有问题

SELECT q.query('.')
FROM @xml.nodes('/Tests/TestAnswers') A(a)
OUTER APPLY a.nodes('*') B(q);

--这将逐行返回所有具有自己文本的节点

SELECT nd.query('.')
FROM @xml.nodes('/Tests/TestAnswers') A(a)
OUTER APPLY a.nodes('*') B(q)
OUTER APPLY q.nodes('//*[text()]') C(nd);

更新

试试这个来获取一个列表，然后使用PIVOTperaInx来获取你的表格格式：

WITH Answers AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS aInx
          ,a.query('.') AS a
    FROM @xml.nodes('/Tests/TestAnswers') A(a)
)
,Questions AS
(
    SELECT aInx
          ,q.query('.') AS q
    FROM Answers
    OUTER APPLY a.nodes('*') B(q)
)
SELECT aInx
      ,nd.value('local-name(.)','nvarchar(max)') AS NodeName
      ,nd.value('text()[1]','nvarchar(max)') AS NodeValue
FROM Questions
OUTER APPLY q.nodes('//*[text()]') C(nd);

推荐阅读