Extract from XML in SQL Server

Question

I want to extract "UDF_B_ACCOUNT.*.TCH" from below XML data in SQL Server.

<jobrundeps>
    <dep id="14175632">
        <type>2</type>
        <jobdepid>7390</jobdepid>
        <override>N</override>
        <met>N</met>
        <filename>/u01/pbm_data/rxad/UDF_B_ACCOUNT.*.TCH</filename>
        <filetype>200</filetype>
        <fileextent>0</fileextent>
        <nodeid>128</nodeid>
    </dep>
</jobrundeps>

question from:https://stackoverflow.com/questions/66053310/extract-from-xml-in-sql-server

Answer 1

SQL Server 2016 onwards.

SQL

DECLARE @xml XML = 
N'<jobrundeps>
    <dep id="14175632">
        <type>2</type>
        <jobdepid>7390</jobdepid>
        <override>N</override>
        <met>N</met>
        <filename>/u01/pbm_data/rxad/UDF_B_ACCOUNT.*.TCH</filename>
        <filetype>200</filetype>
        <fileextent>0</fileextent>
        <nodeid>128</nodeid>
    </dep>
</jobrundeps>';

;WITH rs AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT null)) AS seq
        , value
    FROM STRING_SPLIT((SELECT @xml.value('(/jobrundeps/dep/filename/text())[1]', 'varchar(100)')), '/')
)
SELECT TOP(1) value 
FROM rs
ORDER BY seq DESC;

Output

+---------------------+
|        value        |
+---------------------+
| UDF_B_ACCOUNT.*.TCH |
+---------------------+