使用 Any(,) 分隔符在单列中检索的 XML 值 (2)

2021-10-01 00:00:00 xml sql-server 
declare @x xml =
'<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
   </Tests>
   <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
    </Tests>
</Detials>'

当我这样查询时

SELECT STUFF(
    @x.query('for $a in (*:Detials/Tests/Test/@Name) 
        return <a>{concat(",", $a)}</a>')
    .value('.', 'NVARCHAR(MAX)'), 
    1, 1, '') AS ListOfName

我是这样的

名称列表
测试 1、测试 2、测试 3、测试 4、测试 1、测试 2、测试 3、测试 4

ListofName
Test1,Test2,Test3,Test4,Test1,Test2,Test3,Test4

但是想要根据 <Test> 粉碎 xml，这可以给出如下结果

But Want to shred the xml based on the <Test> Which can give the result like this below

名称列表
测试1、测试2、测试3、测试4
测试1、测试2、测试3、测试4

ListofName
Test1,Test2,Test3,Test4
Test1,Test2,Test3,Test4

请在这里帮助我在使用 query() 和 value() 进行字符串连接之前将原始 XML 分解为单独的行

Please help me here to shred original XML into separate rows before string concatenation with query() and value()

提前致谢，Jayendran

Thanks in Advance ,Jayendran

推荐答案

试试这个解决方案:

declare @x xml =
'<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
   </Tests>
   <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
    </Tests>
</Detials>'

-- Edit 1
SELECT  XmlTable.OriginalID, ROW_NUMBER() OVER(ORDER BY x.XmlCol) AS RowNum, y.ListOfName
FROM (SELECT 1 AS OriginalID, @x AS XmlCol) AS XmlTable -- Edit 2
CROSS APPLY XMLTable.XmlCol.nodes('*:Detials/Tests') AS x(XmlCol)
CROSS APPLY(
    SELECT STUFF(x.XmlCol.query('for $a in (Test/@Name) return <a>{concat(",", $a)}</a>').value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) AS y(ListOfName)
-- End of Edit 1

演示

相关文章