SQL Server通过名称后缀连接XML同级

Question

我有一个DB列，其中的XML字段如下：

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>')

我想选择所有值如下的对象：

Street bike 1, 350 |
Mountain bike 1, 300

正如您所看到的，xml中Type字段的后缀表示如何连接对象：Object1=Value1等。

这是我所做的：

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=49003f33b09df6155e3343760f265d080

有人有主意吗？

Answer 1

这实际上完全可以在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<gt；小提琴

这里的假设是，您希望将Object1加入Value，将Object2加入Value2等。

步骤如下：

• for $e in List/e取List根节点中的每个e节点
• 分配一个变量$t，该变量包含$e的第一个Type子节点的文本
• 在该变量以"Object"开头的位置进行筛选
• 返回一个序列：
  • $e的Name子节点的文本
  • 逗号,
  • 其中Type子节点的第一个e节点是ValueX，其中X是$t的剩余部分。您也可以使用contains来代替sub-string和concat
  • A管|
• 需要强制转换，因为不能同时具有节点和字符串的混合序列
• CCD_ 24将把所有序列连接到一个大字符串中

Answer 2

没有提供可重复的最小示例。所以，我是从臀部投篮。

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;

输出

值

TypeID	对象名称
1	街头自行车1	350
2	山地自行车1	300