sql-server - 如何分解一般定义的相关 XML(没有不同的元素名称)

Question

第三方将提供我需要分解成 SQL Server 关系格式的 XML。 XML 在几个方面是不寻常的

1. 为了更加灵活，XML 提供了一个包含“columnNames”的部分。稍后提供的实际数据没有不同的元素名称，大概我需要根据顺序映射列名称。

2. 提供实际数据的“行”部分没有标题属性或其他关联行中一组字段的方式。

这是数据的简化版本:

<ReportData>
    <ColumnName>SOLD_DATE</ColumnName>
    <ColumnName>STORE_NUMBER</ColumnName>
    <ColumnName>PHONE_NUMBER</ColumnName>
    <ColumnName>FAX_NUMBER</ColumnName>
    <Row>
        <Col>03/31/2016</Col>
        <Col>1234</Col>
        <Col>(425) 673-7065</Col>
        <Col>(425) 278-4974</Col>
    </Row>
    <Row>
        <Col>05/05/2016</Col>
        <Col>3456</Col>
        <Col>(425) 555-7065</Col>
        <Col>(425) 444-4974</Col>
    </Row>
</ReportData>

我怎样才能把它变成一个表格:

Sold_Date   Store_Number    Phone_Number    Fax_Number
3/31/2016   1234            (425) 673-7065  (425) 278-4974
5/5/2016    3456            (425) 555-7065  (425) 444-4974

感谢任何想法。我最初考虑将 OpenXML 与边缘表一起使用以利用父/兄弟节点值，但似乎应该有更好的方法。

Answer 1

如果您需要这样的输出，您需要动态命名您的列。这是不可能的 - 除非您使用动态 SQL。试试这个:

DECLARE @xml XML=
'<ReportData>
    <ColumnName>SOLD_DATE</ColumnName>
    <ColumnName>STORE_NUMBER</ColumnName>
    <ColumnName>PHONE_NUMBER</ColumnName>
    <ColumnName>FAX_NUMBER</ColumnName>
    <Row>
        <Col>03/31/2016</Col>
        <Col>1234</Col>
        <Col>(425) 673-7065</Col>
        <Col>(425) 278-4974</Col>
    </Row>
    <Row>
        <Col>05/05/2016</Col>
        <Col>3456</Col>
        <Col>(425) 555-7065</Col>
        <Col>(425) 444-4974</Col>
    </Row>
</ReportData>';

您唯一可以依赖的是 XML 中的排序

WITH ColumnNames AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ColNr
          ,C.value('.','nvarchar(max)') AS Caption
    FROM @xml.nodes('/ReportData/ColumnName') AS A(C)
)
,RowLines AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNr
          ,R.query('.') AS RowXML
    FROM @xml.nodes('/ReportData/Row') AS A(R)
)
,RowValues AS
(
    SELECT RowNr
          ,ROW_NUMBER() OVER(PARTITION BY RowNr ORDER BY (SELECT NULL)) AS ValNr
          ,C.value('.','nvarchar(max)') AS ColVal
    FROM RowLines
    CROSS APPLY RowXML.nodes('Row/Col') AS A(C)
)
SELECT * 
INTO #tmpResult
FROM RowValues
INNER JOIN ColumnNames ON ColNr=ValNr
ORDER BY RowNr,ValNr;

#tmpResult 表现在有以下内容:

+-------+-------+----------------+-------+--------------+
| RowNr | ValNr | ColVal         | ColNr | Caption      |
+-------+-------+----------------+-------+--------------+
| 1     | 1     | 03/31/2016     | 1     | SOLD_DATE    |
+-------+-------+----------------+-------+--------------+
| 1     | 2     | 1234           | 2     | STORE_NUMBER |
+-------+-------+----------------+-------+--------------+
| 1     | 3     | (425) 673-7065 | 3     | PHONE_NUMBER |
+-------+-------+----------------+-------+--------------+
| 1     | 4     | (425) 278-4974 | 4     | FAX_NUMBER   |
+-------+-------+----------------+-------+--------------+
| 2     | 1     | 05/05/2016     | 1     | SOLD_DATE    |
+-------+-------+----------------+-------+--------------+
| 2     | 2     | 3456           | 2     | STORE_NUMBER |
+-------+-------+----------------+-------+--------------+
| 2     | 3     | (425) 555-7065 | 3     | PHONE_NUMBER |
+-------+-------+----------------+-------+--------------+
| 2     | 4     | (425) 444-4974 | 4     | FAX_NUMBER   |
+-------+-------+----------------+-------+--------------+

现在我们需要一个动态创建的PIVOT语句:

DECLARE @colNames NVARCHAR(MAX)=
(
    STUFF(
    (
        SELECT DISTINCT ',' + Caption + ''
        FROM #tmpResult
        FOR XML PATH('')
    ),1,1,''
    )
);

DECLARE @cmd NVARCHAR(MAX)=
    'SELECT p.*
    FROM
    (
        SELECT RowNr,ColVal,Caption FROM #tmpResult
    ) AS tbl
    PIVOT
    (
        MAX(ColVal) FOR Caption IN(' + @colNames + ')
    ) AS p;';

EXEC(@cmd);

这是结果:

+-------+----------------+----------------+------------+--------------+
| RowNr | FAX_NUMBER     | PHONE_NUMBER   | SOLD_DATE  | STORE_NUMBER |
+-------+----------------+----------------+------------+--------------+
| 1     | (425) 278-4974 | (425) 673-7065 | 03/31/2016 | 1234         |
+-------+----------------+----------------+------------+--------------+
| 2     | (425) 444-4974 | (425) 555-7065 | 05/05/2016 | 3456         |
+-------+----------------+----------------+------------+--------------+

清理

DROP TABLE #tmpResult;