时间戳

首页 > 解决方案 > 具有行和列级别的 XML 到 SQL Server 表

sql - 具有行和列级别的 XML 到 SQL Server 表

问题描述

我有一个名为的表，它在列XMLData中存储一个 XML 文件。DataXML的结构如下：

<?xml version="1.0" encoding="utf-16"?>  
<table>    
    <id>{c566d133-8512-44c3-b6bd-ea0a9fe473d4}</id>    
        <rows>      
            <row>        
                <columns>          
                    <column name="Item" value="ITEM01" type="System.String" />          
                    <column name="Description" value="Description of item 01" type="System.String" />          
                    <column name="Key" value="1" type="System.Int32" />        
                </columns>      
            </row>      
            <row>        
                <columns>          
                    <column name="Item" value="ITEM02" type="System.String" />          
                    <column name="Description" value="Description of item 02" type="System.String" />          
                    <column name="Key" value="2" type="System.Int32" />        
                </columns>      
            </row>    
        </rows>    
    <key>Key</key>    
    <total>0</total>    
    <AddOnKey>0</AddOnKey>    
    <data />    
    <parameters />  
</table>

我想要的是查询 XML 并得到如下结果：

Item      | Description              | Key
------------------------------------------
ITEM01    | Description of item 01   | 1
ITEM02    | Description of item 02   | 2

我试过的是：

SELECT 
    Tbl.Col.value('@name', 'nvarchar(128)'),
    Tbl.Col.value('@value', 'nvarchar(255)')
FROM
    (SELECT CAST(Data AS XML) AS d 
     FROM XMLData) AS d
CROSS APPLY 
    d.nodes('//row/columns/column') Tbl(Col)

但这不会返回所需的结果，因为它会返回两列（名称、值）中的所有内容。我想过往这个方向走：

SELECT 
    Tbl.Col.query('./columns')
FROM
    (SELECT CAST(Data As XML) AS d FROM XMLData) AS d
CROSS APPLY 
    d.nodes('//row') Tbl(Col)

这将返回带有 XML 列的两行：

(No column name)
<columns><column name="Item" value="ITEM01" type="System.String" /><column name="Description" value="Description of item 01" type="System.String" /><column name="Key" value="1" type="System.Int32" /></columns>
<columns><column name="Item" value="ITEM02" type="System.String" /><column name="Description" value="Description of item 02" type="System.String" /><column name="Key" value="2" type="System.Int32" /></columns>

但是我不知道如何获得单独的列。

标签： sqlsql-serverxml

解决方案

您可以尝试对行号进行一些计算，然后按行号使用条件聚合函数。

SELECT
  MAX(CASE WHEN name = 'Item' then value end) 'Item', 
  MAX(CASE WHEN name = 'Description' then value end) 'Description', 
  MAX(CASE WHEN name = 'Key' then value end) 'Key'
FROM (
SELECT 
       Tbl.Col.value('@name', 'nvarchar(128)') name,
       Tbl.Col.value('@value', 'nvarchar(255)') value,
       ((ROW_NUMBER() OVER(ORDER BY Col)-1) / 3)rn 
FROM   (SELECT CAST(Data As XML) AS d FROM XMLData) AS d
CROSS APPLY d.nodes('//row/columns/column') Tbl(Col)
) t1
GROUP BY rn

sqlfiddle

结果

Item    Description             Key
ITEM01  Description of item 01  1
ITEM02  Description of item 02  2

推荐阅读