SQL XQuery to seperate children/descendants

Question

I need to return 2 rows of data from the below XML.

| 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 used before finding the Fault 'name', is below. The results of this shows Question 1 of Fault 2 inside Question 3.

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

Any help would be massively appreciated! I haven't done any XQuery before.

Sorry, the table I've copied in hasn't appeared correctly, tabbing doesn't appear to help. I have created the table on http://www.tablesgenerator.com/markdown_tables. Any other suggestions? Hopefully you get the picture! — WRD299, Sep 18 '18 at 14:26
btw: Your `` is not valid. It's missing a dash between `A954` and `B405...` — Shnugo, Sep 19 '18 at 10:40

score 0 · Accepted Answer · answered Sep 19 '18 at 10:38

You can try it like this:

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>');

--The query will use .nodes() to get multiple rows for repeated elements and the read the content using 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);

The result

+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+
| 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 |
+-------------------------------------+---------------------------+-----------------------+-------------------------+-----------+--------+

This is perfect! Thank you very much @shnugo – WRD299 Sep 19 '18 at 14:23 — WRD299, Sep 19 '18 at 14:23

SQL XQuery to seperate children/descendants

1 Answers1