Joining Xml nodes in SQL Server T-SQL

Question

I have a SQL Server table with a VarChar column called "Reference" and an Xml column called XmlData. I need to be able to join two separate nodes within XmlData, but I'm really not sure how to go about it. This is an example of the data:

Declare     @MyTable    Table
                      ( Reference   VarChar(20)
                      , XmlData     Xml
                      )


Insert      @MyTable
Values    ( 'Reference1'
          , '<MainStuff>
                <FirstGroups>
                    <FirstGroup>
                        <PartOne>ABC</PartOne>
                        <PartTwo>123</PartTwo>
                        <OutputOne>Get this value ABC-123 First</OutputOne>
                    </FirstGroup>
                    <FirstGroup>
                        <PartOne>ABC</PartOne>
                        <PartTwo>456</PartTwo>
                        <OutputOne>Get this value ABC-456 First</OutputOne>
                    </FirstGroup>
                    <FirstGroup>
                        <PartOne>DEF</PartOne>
                        <PartTwo>123</PartTwo>
                        <OutputOne>Get this value DEF-123 First</OutputOne>
                    </FirstGroup>
                </FirstGroups>
                <SecondGroups>
                    <SecondGroup>
                        <Title>ABC-456</Title>
                        <OutputTwo>Get this value ABC-456 Second</OutputTwo>
                    </SecondGroup>
                    <SecondGroup>
                        <Title>ABC-123</Title>
                        <OutputTwo>Get this value ABC-123 Second</OutputTwo>
                    </SecondGroup>
                    <SecondGroup>
                        <Title>DEF-123</Title>
                        <OutputTwo>Get this value DEF-123 Second</OutputTwo>
                    </SecondGroup>
                </SecondGroups>
            </MainStuff>
          '
          )

I want to be able to join FirstGroup to SecondGroup, using a combination of nodes PartOne and PartTwo from FirstGroup to link to Title from SecondGroup. The output would therefore look like this:

Reference	Title	OutputOne	OutputTwo
Reference1	ABC-123	Get this value ABC-123 First	Get this value ABC-123 Second
Reference1	ABC-456	Get this value ABC-456 First	Get this value ABC-456 Second
Reference1	DEF-123	Get this value ABC-123 First	Get this value ABC-123 Second

I can get the first three columns easily enough:

Select      Reference
          , FG.value('(PartOne)[1]', 'VarChar(100)' ) + '-' + FG.value('(PartTwo)[1]', 'VarChar(100)' )     As  Title
          , FG.value('(OutputOne)[1]', 'VarChar(100)' )                                                     As  OutputOne
From        @MyTable                MT
Outer Apply XmlData.nodes('//*/FirstGroup')     As  FirstGroup(FG)

But I'd appreciate some guidance on how I might be able to join the nodes to get the final column.

Answer 1

Add another outer apply for second part and correlate elements

Select      Reference
          , FG.value('(PartOne)[1]', 'VarChar(100)' ) + '-' + FG.value('(PartTwo)[1]', 'VarChar(100)' )     As  Title
          , FG.value('(OutputOne)[1]', 'VarChar(100)' )                                                     As  OutputOne
          , SG.value('(OutputTwo)[1]', 'VarChar(100)' )                                                     As  OutputOne
From        @MyTable                MT
Outer Apply XmlData.nodes('//*/FirstGroup')     As  FirstGroup(FG)
Outer Apply XmlData.nodes('//*/SecondGroup')     As  SecondGroup(SG)
WHERE SG.value('(Title)[1]', 'VarChar(100)' ) = FG.value('(PartOne)[1]', 'VarChar(100)' ) + '-' + FG.value('(PartTwo)[1]', 'VarChar(100)' )

Result

Reference   Title    OutputOne                     OutputOne                    
----------  -------  ----------------------------  -----------------------------
Reference1  ABC-123  Get this value ABC-123 First  Get this value ABC-123 Second
Reference1  ABC-456  Get this value ABC-456 First  Get this value ABC-456 Second
Reference1  DEF-123  Get this value DEF-123 First  Get this value DEF-123 Second