在 SQL Server 2014 中将 XML 数据转换为关系格式时遇到问题 [英] Trouble Getting XML Data into relational format in SQL Server 2014

问题描述

我得到了一些需要粉碎"的 XML 数据(我认为这是正确的术语).那就是我需要把它放到一个 SQL 表中.这是一个示例和一个有效的查询，但我被告知它效率低下.如果您知道更有效的方法，请告诉我.

I'm getting some XML data that I need to 'shred' (I think that's the right term). That is I need to put it into a SQL table. Here's an example, and a query that works, but I was told it was inefficient. Please let me know if you know a more efficient way to do this.

以下是一些 XML 示例，2 个无效的查询和一个有效的查询:

Here's some example XML, 2 queries that don't work, and one that does:

    DECLARE @XmlReportParameters NVARCHAR (MAX) = N'<?xml version="1.0" encoding="utf-16"?>
<Customers>
    <Customer>
        <Name>Sri Patel</Name>
        <FavColors>
            <FavColor>Red</FavColor>
            <FavColor>Blue</FavColor>
            <FavColor>Green</FavColor>
        </FavColors>
    </Customer>
    <Customer>
        <Name>Jane Doe</Name>
        <FavColors>
            <FavColor>Violet</FavColor>
            <FavColor>Mauve</FavColor>
        </FavColors>
    </Customer>
</Customers>
'

DECLARE @doc XML;
DECLARE @XmlTable TABLE
(
    XmlColumn XML NULL
);

SET @doc = @XmlReportParameters;

INSERT INTO @XmlTable
    ( XmlColumn )
VALUES
    ( @doc )

-- Wrong Way
SELECT
    tbl.col.value('(Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(FavColors/FavColor)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer') tbl(col);

-- Still wrong (but I'm not sure why)
SELECT
    tbl.col.value('(../Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(FavColor)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer/FavColors') tbl(col);

-- Right Way
SELECT
    tbl.col.value('(../../Name)[1]', 'nvarchar(max)')      AS CustomerName
   ,tbl.col.value('(.)[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer/FavColors/FavColor') tbl(col);

返回:

CustomerName FavColor
------------ ----------
Sri Patel    Red
Jane Doe     Violet

CustomerName FavColor
------------ ----------
Sri Patel    Red
Jane Doe     Violet

CustomerName FavColor
------------ ----------
Sri Patel    Red
Sri Patel    Blue
Sri Patel    Green
Jane Doe     Violet
Jane Doe     Mauve

推荐答案

正如我之前在另一个问题中告诉过你的，解决方案是对 .nodes() 的分层调用

As I've told you at the other question before, the solution is a hierarchical call to .nodes()

SELECT
    cust.value('(Name/text())[1]', 'nvarchar(max)')      AS CustomerName
   ,col.value('text()[1]', 'nvarchar(max)')       AS FavColor
FROM
    @XmlTable   xt
    CROSS APPLY XmlColumn.nodes('/Customers/Customer') A(cust)
    CROSS APPLY cust.nodes('FavColors/FavColor') AS B(col) ;

这将首先返回所有 和 - 下面 - 它们相关的 :

This will first return all <Customer> and - below them - their related <FavColor>:

Customer    FavColor
Sri Patel   Red
Sri Patel   Blue
Sri Patel   Green
Jane Doe    Violet
Jane Doe    Mauve

这篇关于在 SQL Server 2014 中将 XML 数据转换为关系格式时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！