SQL XQuery 用于分隔子项/后代

Question

>我需要从下面的XML返回2行数据。

| FaultText                 | Question1             | Question2               | Question3 | SOR    |
|---------------------------|-----------------------|-------------------------|-----------|--------|
| Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Bedroom     | Null      | HEA033 |
| Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Living room | Null      | HEA033 |

.XML

<KeyfaxData>
<Fault name="Fault1" type="RD">
<FaultText>Heating: radiator leaking</FaultText>
<TenantID>123456</TenantID>
<TenantText>Mrs Bean</TenantText>
<UserCode>MBEAN</UserCode>
<ScriptPath>
<Question>Q1 = Rad leaking? Yes</Question>
<Question>Q2 = Where? Bedroom</Question>
</ScriptPath>
<Repair name="Repair1">
<RepairCodeEx>HEA033</RepairCodeEx>
<RepairCodeDesc>HEATING SYSTEM:REPAIR LEAK</RepairCodeDesc>
</Repair>
</Fault>
<Fault name="Fault2" type="RD">
<FaultText>Heating: radiator leaking</FaultText>
<TenantID>123457</TenantID>
<TenantText>Mrs Bean</TenantText>
<UserCode>MBEAN1</UserCode>
<ScriptPath>
<Question>Q1 = Rad leaking? Yes</Question>
<Question>Q2 = Where? Living room</Question>
</ScriptPath>
<Repair name="Repair1">
<RepairCodeEx>HEA033</RepairCodeEx>
<RepairCodeDesc>HEATING SYSTEM:REPAIR LEAK</RepairCodeDesc>
</Repair>
</Fault>
<GUID>AA9F632B-8F68-4D30-A954B40517C01947</GUID>
<Status>1</Status>
</KeyfaxData>

在查找错误"名称"之前使用的 SQL 如下所示。其结果在问题 3 中显示了故障 2 的问题 1。

SELECT
exportxml.value('(/KeyfaxData/Fault/FaultText)[1]','nvarchar(500)') as FaultText,
exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[1]','nvarchar(500)') as Question1,
exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[2]','nvarchar(500)') as Question2,
exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[3]','nvarchar(500)') as Question3,
exportxml.value('(/KeyfaxData/Fault/Repair/RepairCode)[1]','nvarchar(500)') as SOR,
FROM KeyFaxHistory

任何帮助将不胜感激！我以前没有做过任何XQuery。

Answer 1

你可以这样尝试：

DECLARE @mockupTable TABLE(exportxml XML);
INSERT INTO @mockupTable(exportxml) VALUES
('<KeyfaxData>
<Fault name="Fault1" type="RD">
<FaultText>Heating: radiator leaking</FaultText>
<TenantID>123456</TenantID>
<TenantText>Mrs Bean</TenantText>
<UserCode>MBEAN</UserCode>
<ScriptPath>
<Question>Q1 = Rad leaking? Yes</Question>
<Question>Q2 = Where? Bedroom</Question>
</ScriptPath>
<Repair name="Repair1">
<RepairCodeEx>HEA033</RepairCodeEx>
<RepairCodeDesc>HEATING SYSTEM:REPAIR LEAK</RepairCodeDesc>
</Repair>
</Fault>
<Fault name="Fault2" type="RD">
<FaultText>Heating: radiator leaking</FaultText>
<TenantID>123457</TenantID>
<TenantText>Mrs Bean</TenantText>
<UserCode>MBEAN1</UserCode>
<ScriptPath>
<Question>Q1 = Rad leaking? Yes</Question>
<Question>Q2 = Where? Living room</Question>
</ScriptPath>
<Repair name="Repair1">
<RepairCodeEx>HEA033</RepairCodeEx>
<RepairCodeDesc>HEATING SYSTEM:REPAIR LEAK</RepairCodeDesc>
</Repair>
</Fault>
<GUID>AA9F632B-8F68-4D30-A954B40517C01947</GUID>
<Status>1</Status>
</KeyfaxData>');

--查询将使用.nodes()获取重复元素的多行，并使用XPath读取内容：

SELECT t.exportxml.value('(/KeyfaxData/GUID/text())[1]','nvarchar(max)') AS GUID_value
,f.value('(FaultText/text())[1]','nvarchar(max)') AS FaultText
,f.value('(ScriptPath/Question[1]/text())[1]','nvarchar(max)') AS Question1
,f.value('(ScriptPath/Question[2]/text())[1]','nvarchar(max)') AS Question2
,f.value('(ScriptPath/Question[3]/text())[1]','nvarchar(max)') AS Question3
,f.value('(Repair/RepairCodeEx/text())[1]','nvarchar(max)') AS SOR
FROM @mockupTable t
CROSS APPLY t.exportxml.nodes('/KeyfaxData/Fault') A(f);

结果

+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+
| GUID_value                          | FaultText                 | Question1             | Question2               | Question3 | SOR    |
+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+
| AA9F632B-8F68-4D30-A954B40517C01947 | Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Bedroom     | NULL      | HEA033 |
+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+
| AA9F632B-8F68-4D30-A954B40517C01947 | Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Living room | NULL      | HEA033 |
+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+