SQL Server join XML siblings by name suffix

Question

I have a DB column with XML field like this:

CREATE TABLE [dbo].[MyObjects]
(
    [Id] [bigint] identity,
    [Details] [xml]
)

INSERT MyObjects(Details) 
VALUES('<List>
    <e>
        <Name>Street bike 1</Name>
        <Type>Object1</Type>
    </e>
    <e>
        <Name>Mountain bike 1</Name>
        <Type>Object2</Type>
    </e>
     <e>
        <Value>350</Value>
        <Type>Value1</Type>
    </e>
    <e>
        <Value>300</Value>
        <Type>Value2</Type>
    </e>
</List>')

I'd like to select all object with values like this:

Street bike 1, 350 |
Mountain bike 1, 300

As you can see suffix of Type field in xml indicates how to join objects: Object1=Value1 etc.

This is as far as I went:

SELECT 
    objects.e.value('(Name/text())[1]','varchar(100)') ObjectName, 
    '0' ObjectValue
FROM 
    MyObjects mo
CROSS APPLY   
    mo.Details.nodes('(List/e[Type[contains(.,"Object")]])') objects(e)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=49003f33b09df6155e343760f265d080

Does anyone have an idea?

Answer 1

This is actually possible purely in XQuery

SELECT mo.Details.query('
  for $e in List/e
  let $t := ($e/Type/text())[1]
  where substring($t, 1, 6) = "Object"
  return (
          ($e/Name/text())[1] cast as xs:string?,
          ",",
          (List/e[Type[text() = concat("Value", substring($t, 7, string-length($t) - 6))]]/Value/text())[1] cast as xs:string?,
          "|"
         )
')
FROM MyObjects mo;

db<>fiddle

The assumption here is that you want to join Object1 to Value and Object2 to Value2 etc.

Steps are as follows:

• for $e in List/e take each e node in the List root node.
• Assign a variable $t containing the text of the first Type child node of $e.
• Filter where that variable begins with "Object".
• Return a sequence of:
  • The text of the Name child node of $e
  • A comma ,
  • The first e node where the Type child node is ValueX where X is the remaining part of $t. You could also use contains instead of sub-string and concat.
  • A pipe |
• The casts are needed because you cannot have mixed sequences of both nodes and strings.
• .query will concatenate all sequences together into one big string.

Answer 2

A minimal reproducible example is not provided. So, I am shooting from the hip.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID BIGINT IDENTITY PRIMARY KEY, Details XML);
INSERT @tbl (Details) VALUES
(N'<List>
    <e>
        <Name>Street bike 1</Name>
        <Type>Object1</Type>
    </e>
    <e>
        <Name>Mountain bike 1</Name>
        <Type>Object2</Type>
    </e>
     <e>
        <Value>350</Value>
        <Type>Value1</Type>
    </e>
    <e>
        <Value>300</Value>
        <Type>Value2</Type>
    </e>
</List>');
-- DDL and sample data population, end

DECLARE @Object VARCHAR(10) = 'Object'
    , @Value VARCHAR(10) = 'Value';

;WITH rs AS
(
    SELECT e.value('(Name/text())[1]','VARCHAR(100)') AS ObjectName
        , REPLACE(e.value('(Type/text())[1]','VARCHAR(100)'),@Object,'') AS TypeID
    FROM @tbl
    CROSS APPLY Details.nodes('(/List/e[Type[contains(.,sql:variable("@Object"))]])') t(e)
), rs2 AS
(
    SELECT e.value('(Value/text())[1]','VARCHAR(100)') AS [Value]
        , REPLACE(e.value('(Type/text())[1]','VARCHAR(100)'),@Value,'') AS TypeID
    FROM @tbl
    CROSS APPLY Details.nodes('(/List/e[Type[contains(.,sql:variable("@Value"))]])') t(e)
)
SELECT rs.TypeID, rs.ObjectName, rs2.[Value] 
FROM rs2 
    INNER JOIN rs ON rs2.TypeID = rs.TypeID;

Output

TypeID	ObjectName	Value
1	Street bike 1	350
2	Mountain bike 1	300