Trying to join elements of XML with SQL

Question

I have this following query.

The RDBMS is Microsoft SQL Server 2019 Developer Edition (64-bit) on Windows 10 Enterprise 10.0

I'm trying to join XML elements using SQL with no success.

I want to get all nodes of custom_rules element joined with their Translations which are in the bottom of the XML.

The join is based on CustomRule CGID attribute that matches with element ID of element Captions.

When I make the join the joined column returns Null

DECLARE @xml as xml = N'
        <BRDatasetNode RootNodeType="Entersoft.Framework.Platform.CustomCommands.BRDatasetNode, ESCustomNode" Version="2" CGID="42d7a588-adb7-4bf1-8ed0-fdefe8038267" Dataset="ESGOPerson">
          <system_rules CGID="beeeb555-749a-411d-8021-5ef9d521ad8e" />
          <custom_rules CGID="13758bd2-68c0-4f52-b78e-80825016c77b">
            <CustomRule CGID="e7d23139-6880-41b0-b8c6-22212a510108" use_case="bf5c89d2-93d9-4ea7-858f-bba9fdf0d957">
            </CustomRule>
            <CustomRule CGID="491bfdbc-58ab-41c7-8915-d94a51b55d26">
            </CustomRule>
          </custom_rules>
          <Translations>
            <ESCaptionsDS>
          <Captions>
            <ID>e7d23139-6880-41b0-b8c6-22212a510108</ID>
            <Suffix>Title</Suffix>
            <LangID>el</LangID>
            <Caption>Κανόνες για επωφελούμενα πρόσωπα</Caption>
          </Captions>
          <Captions>
            <ID>491bfdbc-58ab-41c7-8915-d94a51b55d26</ID>
            <Suffix>Title</Suffix>
            <LangID>el</LangID>
            <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
          </Captions>
        </ESCaptionsDS>
          </Translations>
        </BRDatasetNode>'
        
        
SELECT
    CAST(u.n.value('@CGID', 'varchar(max)') AS varchar(max)) , zz.Caption 
FROM
    @xml.nodes('/BRDatasetNode/custom_rules/CustomRule') AS u (n)       
CROSS APPLY
    (SELECT
         CAST(u.n.value('Caption[1]', 'varchar(max)') AS varchar(max)) as Caption
     FROM
         @xml.nodes('/BRDatasetNode/Translations/ESCaptionsDS/Captions') AS z (n) 
     WHERE
         CONVERT(nvarchar(max), z.n.value('ID[1]', 'nvarchar(max)')) = CONVERT(nvarchar(max), u.n.value('./@CGID', 'nvarchar(max)'))
    ) AS zz

The result is

(No column name)	Caption
e7d23139-6880-41b0-b8c6-22212a510108	NULL
491bfdbc-58ab-41c7-8915-d94a51b55d26	NULL

Answer 1

Please try the following solution for MS SQL Server.

It is using XQuery and its FLWOR expression for the join.

The actual join is implemented for two sequences: $x and $y.

The CTE returns relevant <Captions> elements.

SQL

DECLARE @xml XML = 
N'<BRDatasetNode RootNodeType="Entersoft.Framework.Platform.CustomCommands.BRDatasetNode, ESCustomNode"
               Version="2" CGID="42d7a588-adb7-4bf1-8ed0-fdefe8038267"
               Dataset="ESGOPerson">
    <system_rules CGID="beeeb555-749a-411d-8021-5ef9d521ad8e"/>
    <custom_rules CGID="13758bd2-68c0-4f52-b78e-80825016c77b">
        <CustomRule CGID="e7d23139-6880-41b0-b8c6-22212a510108"
                    use_case="bf5c89d2-93d9-4ea7-858f-bba9fdf0d957">
        </CustomRule>
        <CustomRule CGID="491bfdbc-58ab-41c7-8915-d94a51b55d26">
        </CustomRule>
        <CustomRule CGID="577f5ec8-5058-490a-98b4-f87b663f3b67">
        </CustomRule>
        <CustomRule CGID="70e93dee-051f-40fd-bbb1-b60d084e0cb0">
        </CustomRule>
    </custom_rules>
    <Translations>
        <ESCaptionsDS>
            <Captions>
                <ID>e7d23139-6880-41b0-b8c6-22212a510108</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Κανόνες για επωφελούμενα πρόσωπα</Caption>
            </Captions>
            <Captions>
                <ID>ffeee6da-5239-484a-b701-ce9d1ab7b05f</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Κανόνες για επωφελούμενα πρόσωπα</Caption>
            </Captions>
            <Captions>
                <ID>8bae1ba8-e6ed-4e0a-b02d-1f3e73034801</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση πεδίων κατά την επισύναψη εγγράφου</Caption>
            </Captions>
            <Captions>
                <ID>b85f3890-bcd9-43a4-8619-a061a97c9747</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση πεδίων κατά την επισύναψη εγγράφου</Caption>
            </Captions>
            <Captions>
                <ID>722e1fce-8d04-4f61-9272-e31bce9f984f</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Maximum Entries</Caption>
            </Captions>
            <Captions>
                <ID>1c614046-c7c5-4dd3-87d0-dabec01c15ab</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ενημέρωση χαρακτηρισμού ανήλικος</Caption>
            </Captions>
            <Captions>
                <ID>09436c79-3bf0-4c46-80fb-93a20e1e7924</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ενημέρωση χαρακτηρισμού ανήλικος</Caption>
            </Captions>
            <Captions>
                <ID>d9575b83-fa08-4022-a54a-7633ea802fc0</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Maximum Entries</Caption>
            </Captions>
            <Captions>
                <ID>ad7e28a5-3366-4487-bc99-06b018e29609</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης Φορέα</Caption>
            </Captions>
            <Captions>
                <ID>23c54352-3496-45f4-9551-45f7a29056f0</ID>
                <Suffix>Message</Suffix>
                <LangID>el</LangID>
                <Caption>Παρακαλώ συμπληρώστε Φορέα.</Caption>
            </Captions>
            <Captions>
                <ID>a0903e5a-dc7b-40d7-bef1-ada1af724243</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Κανόνες για επωφελούμενα πρόσωπα</Caption>
            </Captions>
            <Captions>
                <ID>491bfdbc-58ab-41c7-8915-d94a51b55d26</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>96c844ac-41be-4fe1-8c54-360c830351f1</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>0c08d413-0c08-4bb5-96a7-93322fdfd955</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>9cab91d1-202d-490e-8ee2-4fee30db56d2</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>9a865e63-0605-49a3-aa03-08504831e136</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Maximum Entries</Caption>
            </Captions>
            <Captions>
                <ID>65f75065-376d-40f8-9b2a-733bcc597247</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>577f5ec8-5058-490a-98b4-f87b663f3b67</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>91702e0f-3f55-412d-9cde-c0161d7cfcbf</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>5660b621-c7d8-41bf-9526-a880e89e2e09</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>96adc51f-177a-41a5-ba95-6ebd3f96d25a</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>cecb7870-898f-42bf-a3a0-4d436e72c64b</ID>
                <Suffix>Message</Suffix>
                <LangID>el</LangID>
                <Caption>Παρακαλώ συμπληρώστε ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>5c833d3e-00c9-4f5e-a8b3-3ba533a20a31</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος συμπλήρωσης ΑΦΜ</Caption>
            </Captions>
            <Captions>
                <ID>70e93dee-051f-40fd-bbb1-b60d084e0cb0</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος στοιχείων καταχώρησης</Caption>
            </Captions>
            <Captions>
                <ID>cfdf614e-0835-425e-ab79-870db327fafa</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>ac617fca-5fd3-47d1-909f-131bfb8c7d58</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Έλεγχος στοιχείων καταχώρησης</Caption>
            </Captions>
            <Captions>
                <ID>787963fb-304f-4e60-a3b5-0fde2de50ece</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
            <Captions>
                <ID>66bc1cd4-a7e2-47e1-b4b9-76276c73480c</ID>
                <Suffix>Message</Suffix>
                <LangID>el</LangID>
                <Caption>Δεν έχετε συμπληρώσει όλα τα πεδία (Επώνυμο, Όνομα, Φύλο, Ημ/νία γέννησης, Αριθμός εγγράφου)</Caption>
            </Captions>
            <Captions>
                <ID>512b35f8-3190-493c-b680-ac8caf713fcb</ID>
                <Suffix>Title</Suffix>
                <LangID>el</LangID>
                <Caption>Ανάθεση Ημ/νίας εγγραφής</Caption>
            </Captions>
        </ESCaptionsDS>
    </Translations>
</BRDatasetNode>';

;WITH rs (xmldata) AS
(
    SELECT @xml.query('
       for $x in /BRDatasetNode/custom_rules/CustomRule/@CGID,
          $y in /BRDatasetNode/Translations/ESCaptionsDS/Captions/ID/text()
       where lower-case($y) eq lower-case($x)
       return /BRDatasetNode/Translations/ESCaptionsDS/Captions[ID/text() = $y]
    ')
)
SELECT c.value('(ID/text())[1]', 'UNIQUEIDENTIFIER') AS ID
    , c.value('(Caption/text())[1]', 'NVARCHAR(100)') AS Caption
FROM rs CROSS APPLY xmldata.nodes('/Captions') AS t(c);

Output

+--------------------------------------+----------------------------------+
|                  ID                  |             Caption              |
+--------------------------------------+----------------------------------+
| E7D23139-6880-41B0-B8C6-22212A510108 | Κανόνες για επωφελούμενα πρόσωπα |
| 491BFDBC-58AB-41C7-8915-D94A51B55D26 | Ανάθεση Ημ/νίας εγγραφής         |
| 577F5EC8-5058-490A-98B4-F87B663F3B67 | Έλεγχος συμπλήρωσης ΑΦΜ          |
| 70E93DEE-051F-40FD-BBB1-B60D084E0CB0 | Έλεγχος στοιχείων καταχώρησης    |
+--------------------------------------+----------------------------------+