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;
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 eache
node in theList
root node.- Assign a variable
$t
containing the text of the firstType
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 theType
child node isValueX
whereX
is the remaining part of$t
. You could also usecontains
instead ofsub-string
andconcat
. - A pipe
|
- The text of the
- 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.