XML XQuery Find sub nodes

Question

I have a 20k line XML file in a SQL table with XML data type.

Below is subsection of the file.

I need to find all System nodes for a particular DF ID

Here is my query, the query returns all system nodes not the system nodes for DF Id=1.

select station, 
  x.y.value('@id[1]','int') AS DFR_Id,
  z.y.value('local-name(.)', 'nvarchar(128)') AS elem_name,
  z.y.value('text()[1]', 'nvarchar(max)') AS elem_text
from DME_Settings_Baseline e 
OUTER APPLY e.configxml.nodes('SubStation/DFs/DF') as X(Y)
OUTER APPLY e.configxml.nodes('SubStation/ DFs/DF/System/*') as Z(Y)
WHERE X.Y.value('@id[1]','int')=1'

---

<SubStation id="10" name="West">
<DFs>
<DF id="1">
  <IPAddress>155.100.1.1</IPAddress>
  <System>
    <DfrId>1</DfrId>
    <RecordingDeviceId>M9000</RecordingDeviceId>
    <SampleRate>4800</SampleRate>
    <LineFrequency>60</LineFrequency>
    <ExportRate>60</ExportRate>
    <ACount>56</ACount>
    <DigitalsCount>928</DigitalsCount>
    <PrefaultTime>500</PrefaultTime>
    <PostfaultTime>500</PostfaultTime>
    <LtrPrefaultTime>40</LtrPrefaultTime>
    <LtrPostfaultTime>40</LtrPostfaultTime>
    <ChatterLimit>20</ChatterLimit>
    <ChatterRate>2.0</ChatterRate>
    <TriggerLimit>500</TriggerLimit>
    <DatabaseDataType>BINARY</DatabaseDataType>
  </System>
</DF>
<DF id="2">
  <IPAddress>155.100.1.1</IPAddress>
  <System>
    <DfrId>2</DfrId>
    <RecordingDeviceId>M9000</RecordingDeviceId>
    <SampleRate>4800</SampleRate>
    <LineFrequency>60</LineFrequency>
    <ExportRate>60</ExportRate>
    <ACount>56</ACount>
    <DigitalsCount>928</DigitalsCount>
    <PrefaultTime>500</PrefaultTime>
    <PostfaultTime>500</PostfaultTime>
    <LtrPrefaultTime>40</LtrPrefaultTime>
    <LtrPostfaultTime>40</LtrPostfaultTime>
    <ChatterLimit>20</ChatterLimit>
    <ChatterRate>2.0</ChatterRate>
    <TriggerLimit>400</TriggerLimit>
    <DatabaseDataType>BINARY</DatabaseDataType>
    <Ps>S</Ps>
  </System>
</DF>

Answer 1

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, configxml XML);
INSERT INTO @tbl (configxml) VALUES
(N'<SubStation id="10" name="West">
    <DFs>
        <DF id="1">
            <IPAddress>155.100.1.1</IPAddress>
            <System>
                <DfrId>1</DfrId>
                <RecordingDeviceId>M9000</RecordingDeviceId>
                <SampleRate>4800</SampleRate>
                <LineFrequency>60</LineFrequency>
                <ExportRate>60</ExportRate>
                <ACount>56</ACount>
                <DigitalsCount>928</DigitalsCount>
                <PrefaultTime>500</PrefaultTime>
                <PostfaultTime>500</PostfaultTime>
                <LtrPrefaultTime>40</LtrPrefaultTime>
                <LtrPostfaultTime>40</LtrPostfaultTime>
                <ChatterLimit>20</ChatterLimit>
                <ChatterRate>2.0</ChatterRate>
                <TriggerLimit>500</TriggerLimit>
                <DatabaseDataType>BINARY</DatabaseDataType>
            </System>
        </DF>
        <DF id="2">
            <IPAddress>155.100.1.1</IPAddress>
            <System>
                <DfrId>2</DfrId>
                <RecordingDeviceId>M9000</RecordingDeviceId>
                <SampleRate>4800</SampleRate>
                <LineFrequency>60</LineFrequency>
                <ExportRate>60</ExportRate>
                <ACount>56</ACount>
                <DigitalsCount>928</DigitalsCount>
                <PrefaultTime>500</PrefaultTime>
                <PostfaultTime>500</PostfaultTime>
                <LtrPrefaultTime>40</LtrPrefaultTime>
                <LtrPostfaultTime>40</LtrPostfaultTime>
                <ChatterLimit>20</ChatterLimit>
                <ChatterRate>2.0</ChatterRate>
                <TriggerLimit>400</TriggerLimit>
                <DatabaseDataType>BINARY</DatabaseDataType>
                <Ps>S</Ps>
            </System>
        </DF>
    </DFs>
</SubStation>');
-- DDL and sample data population, end

SELECT t.c.value('@id','INT') AS DFR_Id,
    z.y.value('local-name(.)', 'NVARCHAR(128)') AS elem_name,
    z.y.value('text()[1]', 'NVARCHAR(MAX)') AS elem_text
FROM @tbl AS e 
    CROSS APPLY e.configxml.nodes('/SubStation/DFs/DF') as t(c)
    CROSS APPLY t.c.nodes('System/*') as z(y)
WHERE t.c.value('@id','INT')=1;

Output

+--------+-------------------+-----------+
| DFR_Id |     elem_name     | elem_text |
+--------+-------------------+-----------+
|      1 | DfrId             | 1         |
|      1 | RecordingDeviceId | M9000     |
|      1 | SampleRate        | 4800      |
|      1 | LineFrequency     | 60        |
|      1 | ExportRate        | 60        |
|      1 | ACount            | 56        |
|      1 | DigitalsCount     | 928       |
|      1 | PrefaultTime      | 500       |
|      1 | PostfaultTime     | 500       |
|      1 | LtrPrefaultTime   | 40        |
|      1 | LtrPostfaultTime  | 40        |
|      1 | ChatterLimit      | 20        |
|      1 | ChatterRate       | 2.0       |
|      1 | TriggerLimit      | 500       |
|      1 | DatabaseDataType  | BINARY    |
+--------+-------------------+-----------+