Home > Blockchain >  SQL Server join XML siblings by name sufix
SQL Server join XML siblings by name sufix

Time:08-24

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 sufix 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?

CodePudding user response:

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

CodePudding user response:

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.
  • Related