sql - 在 SQL Server 中提取具有不一致属性和元素的嵌套 XML 数据

Question

我有一个包含 2 列的现有表:唯一 ID，xml 格式的数据。后者具有嵌套结构但未标准化(请参见下面的示例)。想将每个 ID 的所有 xml 信息提取为以下格式:

ID | Variable | Value  

现有表排列如下:

ID |   Data 
---+-------------------------------------------------------
1  |   <ID><Input_CCY style="Input"> USD </Input_CCY>.....
2  |   <ID><Input_CCY style="Input"> GBP </Input_CCY>.....

数据列是 XML 类型，每行的示例如下，尽管每个 ID 的字段数会有所不同:

<risk>
  <Input_CCY style="Input">USD</Input_CCY>
  <Input_LastYear style="Output" formula="=SUM(E65:E68)">500</Input_LastYear>
  <table name="info" header_row="1">
    <Row name="" iRow="2">
      <Col style="Input" name="Ref" iCol="1" />AAA</Col>
      <Col style="Input" name="Location" iCol="2">London</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Input" name="Ref" iCol="1" />BBB</Col>
      <Col style="Input" name="Location" iCol="2">Edinburgh</Col>
    ...
    </Row>
    ...
  </table>
  <table name="summary" header_row="1">
    <Row name="" iRow="2">
      <Col style="Output" name="Status" iCol="1" />Amber</Col>
      <Col style="Output" name="Referral_Bonus" iCol="2">No</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Normal" name="Status" iCol="1" />Green</Col>
      <Col style="Normal" name="Referral_Bonus" iCol="2">YES</Col>
    ...
    </Row>
    ...
  </table>
</risk>

我尝试在 SQL 中使用 CROSS APPLY 方法先提取单个单元格信息，效果很好:

SELECT 
    T.[ID] as [ID], 
    a.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_1_Name],
    a.b.value('.', 'NVARCHAR(100)') As [Node_1_Value] -- value,
    c.b.value('.', 'NVARCHAR(100)') As [Node_1_Type] -- value
FROM 
    [dbo].[all] AS T
CROSS APPLY 
    T.[data].nodes('/risk/*') AS a(b) 
OUTER APPLY 
    a.b.nodes('@*') AS c(b) 
WHERE
    a.b.value('fn:local-name(.)', 'VARCHAR(100)') != 'table' -- tables

此查询返回:

ID  |   Node_1_Name   | Node_1_Value  |  Node_1_Type
----+-----------------+---------------+----------------
1   |   Input_CCY     | USD           |  Input
1   |   Input_LastYear| 500           |  Output
1   |   Input_LastYear| 500           |  =SUM(E65:E68)
2   |   Input_CCY     | GBP           |  Input
...

但是当我添加更多级别的CROSS APPLY 以深入到表的嵌套级别时:

SELECT 
    T.[ID] as [ID], 
    a.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_1_Name],
    c.b.value('.', 'NVARCHAR(100)') As [Node_1_Attribute_Value], 
    g.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_2_Name],
    h.b.value('.', 'NVARCHAR(100)') As [Node_2_Attribute_Value], 
    e.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_3_Name], 
    e.b.value('.', 'NVARCHAR(100)') As [Node_3_Element], 
    f.b.value('fn:local-name(.)', 'NVARCHAR(100)') As [Node_3_Attribute], 
    f.b.value('.', 'NVARCHAR(100)') As [Node_3_Attribute_Value] 
FROM 
    [dbo].[all] AS T
CROSS APPLY 
    T.[data].nodes('/risk/*') AS a(b) 
OUTER APPLY 
    a.b.nodes('@*') AS c(b) 
CROSS APPLY 
    T.[data].nodes('/risk/table/*') g(b) 
OUTER APPLY 
    g.b.nodes('@name') h(b) 
CROSS APPLY 
    T.[data].nodes('/risk/table/Row/*') e(b) 
OUTER APPLY 
    e.b.nodes('@name') f(b) 
WHERE
    a.b.value('fn:local-name(.)', 'VARCHAR(100)') = 'table' 
    AND c.b.value('fn:local-name(.)', 'NVARCHAR(100)') = 'name'
    AND f.b.value('fn:local-name(.)', 'NVARCHAR(100)') = 'name' 

结果表中似乎有大量重复项。例如，即使对于单个 ID(即原始数据集中的 1 行)，第一个表也会有 30k+ 行(应该是 8 x 24 表 = 192 行)。我怀疑我滥用了 CROSS APPLY，但无法弄清楚。

这是我希望将表格元素提取到的格式:

ID   |    Table Name    |   Row#   |    Row Name   |   Col#   |   Col Name    |   Value  | Type (input/output)

或者也许我应该完全使用其他一些功能？

我拆分了单个单元格和表格节点的编码，因为我认为先给定它们的结构，然后再重新组合它们，然后再分别为它们编码会更容易。如果有一种方法可以一次性完成，那就太受欢迎了!

Answer 1

整个事情取决于您事先知道的内容。:

模拟您的问题的模型:

DECLARE @YourTable TABLE(ID INT IDENTITY, [Data] XML);
INSERT INTO @YourTable VALUES
(N'<risk>
  <Input_CCY style="Input">USD</Input_CCY>
  <Input_LastYear style="Output" formula="=SUM(E65:E68)">500</Input_LastYear>
  <table name="info" header_row="1">
    <Row name="" iRow="2">
      <Col style="Input" name="Ref" iCol="1" >AAA</Col>
      <Col style="Input" name="Location" iCol="2">London</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Input" name="Ref" iCol="1" >BBB</Col>
      <Col style="Input" name="Location" iCol="2">Edinburgh</Col>
    ...
    </Row>
    ...
  </table>
  <table name="summary" header_row="1">
    <Row name="" iRow="2">
      <Col style="Output" name="Status" iCol="1" >Amber</Col>
      <Col style="Output" name="Referral_Bonus" iCol="2">No</Col>
    ...
    </Row>
    <Row name="" iRow="3">
      <Col style="Normal" name="Status" iCol="1" >Green</Col>
      <Col style="Normal" name="Referral_Bonus" iCol="2">YES</Col>
    ...
    </Row>
    ...
  </table>
</risk>');

--此查询将返回列作为 EAV。
--如果你事先不知道表和列，这很好。
--我建议将返回的集合加载到临时表中并从那里继续:

SELECT t.ID
      ,t.[Data].value('(/risk/Input_CCY/@style)[1]','nvarchar(100)') AS Input_CCY_Style
      ,t.[Data].value('(/risk/Input_CCY/text())[1]','nvarchar(100)') AS Input_CCY_Content
      ,t.[Data].value('(/risk/Input_LastYear/@style)[1]','nvarchar(100)') AS Input_LastYear_Style
      ,t.[Data].value('(/risk/Input_LastYear/@formula)[1]','nvarchar(100)') AS Input_LastYear_Formula
      ,t.[Data].value('(/risk/Input_LastYear/text())[1]','nvarchar(100)') AS Input_LastYear_Content
      ,tbl.value('@name','nvarchar(250)') AS Table_Name
      ,rw.value('@iRow','int') AS Row_iRow
      ,cl.value('@name','nvarchar(250)') AS [Col_Name]
      ,cl.value('@iCol','nvarchar(250)') AS Col_iCol
      ,cl.value('text()[1]','nvarchar(150)') AS Col_Content
FROM @YourTable t
CROSS APPLY t.[Data].nodes('/risk/table') A(tbl)
CROSS APPLY tbl.nodes('Row') B(rw)
CROSS APPLY rw.nodes('Col') C(cl);

--此查询将按已知名称选择列。
-- 正如我在这里所说的那样，您将获得所有内容，但如果实际表中不存在所有列，则所有列都将返回 NULL。

SELECT t.ID
      ,t.[Data].value('(/risk/Input_CCY/@style)[1]','nvarchar(100)') AS Input_CCY_Style
      ,t.[Data].value('(/risk/Input_CCY/text())[1]','nvarchar(100)') AS Input_CCY_Content
      ,t.[Data].value('(/risk/Input_LastYear/@style)[1]','nvarchar(100)') AS Input_LastYear_Style
      ,t.[Data].value('(/risk/Input_LastYear/@formula)[1]','nvarchar(100)') AS Input_LastYear_Formula
      ,t.[Data].value('(/risk/Input_LastYear/text())[1]','nvarchar(100)') AS Input_LastYear_Content
      ,tbl.value('@name','nvarchar(250)') AS Table_Name
      ,rw.value('@iRow','int') AS Row_iRow

      ,rw.value('(Col[@name="Ref"]/@iCol)[1]','nvarchar(150)') AS Col_Ref_iCol
      ,rw.value('(Col[@name="Ref"]/@style)[1]','nvarchar(150)') AS Col_Ref_Style
      ,rw.value('(Col[@name="Ref"]/text())[1]','nvarchar(150)') AS Col_Ref_Content

      ,rw.value('(Col[@name="Location"]/@iCol)[1]','nvarchar(150)') AS Col_Location_iCol
      ,rw.value('(Col[@name="Location"]/@style)[1]','nvarchar(150)') AS Col_Location_Style
      ,rw.value('(Col[@name="Location"]/text())[1]','nvarchar(150)') AS Col_Location_Content

      ,rw.value('(Col[@name="Status"]/@iCol)[1]','nvarchar(150)') AS Col_Status_iCol
      ,rw.value('(Col[@name="Status"]/@style)[1]','nvarchar(150)') AS Col_Status_Style
      ,rw.value('(Col[@name="Status"]/text())[1]','nvarchar(150)') AS Col_Status_Content

      ,rw.value('(Col[@name="Referral_Bonus"]/@iCol)[1]','nvarchar(150)') AS Col_Referral_Bonus_iCol
      ,rw.value('(Col[@name="Referral_Bonus"]/@style)[1]','nvarchar(150)') AS Col_Referral_Bonus_Style
      ,rw.value('(Col[@name="Referral_Bonus"]/text())[1]','nvarchar(150)') AS Col_Referral_Bonus_Content
FROM @YourTable t
CROSS APPLY t.[Data].nodes('/risk/table') A(tbl)
CROSS APPLY tbl.nodes('Row') B(rw);

如果您事先知道所有的表和列，我建议采用第二种方法，但每个表单独一个语句。