XML sütunundaki SQL Server jadvali - takrorlanuvchi tag ismini bir ustunga birlashtirish

SQL Serverda xml ma'lumotlar ustunini so'rov qilishim kerak va xml ichidagi ba'zi bir teglardan qanday qilib so'roq qilishim mumkinligi haqida ba'zi yordamga muhtojman.

Mening so'rovimni sinab ko'rish uchun foydalanadigan o'yinchoq ma'lumotlar to'plami:


    
        
    
    
        
        
        
        
    

So'rovimdan erishmoqchi bo'lgan natijalar quyidagicha:

 CT1          CN1    CT2         CN2
 -----------------------------------------------------
 INEQUALITY   col1   INCLUDE     col2, col3, col4, col5

Men ega bo'lgan masala "CN2" ustunini birgalikda joylashtirish uchun xml so'rovini bajarishga qodir. Birinchi 3 ustunni olish uchun so'rovni aniqladim, ammo oxirgi (CN2) uchun emas.

Bu erda hozirda mavjud bo'lgan so'rov (#queryPlan xml saqlash uchun yaratgan temp jadval, query_plan xml bilan ustun):

SELECT 
    query_plan.value('(/SampleData/ColumnGroup/@Usage)[1]', 'varchar(max)') as [CT1],
    query_plan.value('(/SampleData/ColumnGroup/Column/@Name)[1]', 'varchar(max)') as [CN1],
    query_plan.value('(/SampleData/ColumnGroup/@Usage)[2]', 'varchar(max)') as [CT2]
FROM 
    #queryPlan

Lekin bu so'rov uchun to'rtinchi ustun bilan hech qanday imkoniyat yo'q edi. Men yashayotgan muammoni taglavhani bitta taglavaga birlashtiradi. Buni qanday amalga oshirishim mumkinligi haqida har qanday takliflar bormi? Rahmat.

EDIT: Mana, men erishgan yutuq.

SELECT  CT1 = t.query_plan.value('(SampleData/ColumnGroup/@Usage)[1]', 'NVARCHAR(MAX)'),
    CN1 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                FROM    t.query_plan.nodes('SampleData/ColumnGroup[1]/Column') AS i (X)
                FOR xml PATH('')), 1, 1, ''),
    CT2 = t.query_plan.value('(SampleData/ColumnGroup/@Usage)[2]', 'NVARCHAR(MAX)'),
    CN2 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                FROM    t.query_plan.nodes('SampleData/ColumnGroup[2]/Column') AS i (X)
                FOR xml PATH('')), 1, 1, '')
FROM    #queryPlan t;
2

6 javoblar

Birinchidan, turli elementlarni satrlarga siqib, keyin vergilarni qo'shib, keyin for xml PATH() yordamida satrlarni bitta ustunga qaytarish uchun buni amalga oshirishning bir usuli yo'q deb o'ylayman:

DECLARE @T TABLE (X XML);
INSERT @T (X)
VALUES ('

    
        
    
    
        
        
        
        
    
');

SELECT  CT1 = t.x.value('(SampleData/ColumnGroup/@Usage)[1]', 'NVARCHAR(MAX)'),
        CN1 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                    FROM    t.X.nodes('SampleData/ColumnGroup[1]/Column') AS i (X)
                    FOR xml PATH('')), 1, 1, ''),
        CT2 = t.x.value('(SampleData/ColumnGroup/@Usage)[2]', 'NVARCHAR(MAX)'),
        CN2 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                    FROM    t.X.nodes('SampleData/ColumnGroup[2]/Column') AS i (X)
                    FOR xml PATH('')), 1, 1, '')
FROM    @T t;
1
qo'shib qo'ydi
Rahmat, bu ishlagan! Men bir nechta o'zgarishlarni amalga oshirishim kerak edi va men o'zimning javobimni ko'rsataman.
qo'shib qo'ydi muallif NickB, manba

Birinchidan, turli elementlarni satrlarga siqib, keyin vergilarni qo'shib, keyin for xml PATH() yordamida satrlarni bitta ustunga qaytarish uchun buni amalga oshirishning bir usuli yo'q deb o'ylayman:

DECLARE @T TABLE (X XML);
INSERT @T (X)
VALUES ('

    
        
    
    
        
        
        
        
    
');

SELECT  CT1 = t.x.value('(SampleData/ColumnGroup/@Usage)[1]', 'NVARCHAR(MAX)'),
        CN1 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                    FROM    t.X.nodes('SampleData/ColumnGroup[1]/Column') AS i (X)
                    FOR xml PATH('')), 1, 1, ''),
        CT2 = t.x.value('(SampleData/ColumnGroup/@Usage)[2]', 'NVARCHAR(MAX)'),
        CN2 = STUFF((SELECT CONCAT(',', i.X.value('@Name', 'NVARCHAR(MAX)'))
                    FROM    t.X.nodes('SampleData/ColumnGroup[2]/Column') AS i (X)
                    FOR xml PATH('')), 1, 1, '')
FROM    @T t;
1
qo'shib qo'ydi
Rahmat, bu ishlagan! Men bir nechta o'zgarishlarni amalga oshirishim kerak edi va men o'zimning javobimni ko'rsataman.
qo'shib qo'ydi muallif NickB, manba

Siz shunday bir narsa qidiryapsizmi?

DECLARE @xml XML=
N'
  
    
  
  
    
    
    
    
  
';
select @xml

SELECT cg.value('(@Usage)[1]', 'varchar(max)') as ColumnGroupUsage
      ,c.value('(@Name)[1]','varchar(max)') AS ColumnName
FROM @xml.nodes(N'SampleData/ColumnGroup') AS A(cg)
OUTER APPLY cg.nodes(N'Column') AS B(c)

Natija

INEQUALITY  col1
INCLUDE     col2
INCLUDE     col3
INCLUDE     col4
INCLUDE     col5
0
qo'shib qo'ydi
Javobingiz uchun rahmat, lekin aniq emas. Savolning tavsifida ko'rsatilganidek, bir qator (bir nechta ustunlar emas) natijasini olishim kerak. Buning sababi shundaki, #queryPlan temp stolida bir nechta satr bor, natijalar olishim kerak.
qo'shib qo'ydi muallif NickB, manba
Ko'p qatorlar * bir nechta ustunlar emas
qo'shib qo'ydi muallif NickB, manba

Siz shunday bir narsa qidiryapsizmi?

DECLARE @xml XML=
N'
  
    
  
  
    
    
    
    
  
';
select @xml

SELECT cg.value('(@Usage)[1]', 'varchar(max)') as ColumnGroupUsage
      ,c.value('(@Name)[1]','varchar(max)') AS ColumnName
FROM @xml.nodes(N'SampleData/ColumnGroup') AS A(cg)
OUTER APPLY cg.nodes(N'Column') AS B(c)

Natija

INEQUALITY  col1
INCLUDE     col2
INCLUDE     col3
INCLUDE     col4
INCLUDE     col5
0
qo'shib qo'ydi
Javobingiz uchun rahmat, lekin aniq emas. Savolning tavsifida ko'rsatilganidek, bir qator (bir nechta ustunlar emas) natijasini olishim kerak. Buning sababi shundaki, #queryPlan temp stolida bir nechta satr bor, natijalar olishim kerak.
qo'shib qo'ydi muallif NickB, manba
Ko'p qatorlar * bir nechta ustunlar emas
qo'shib qo'ydi muallif NickB, manba

Agar ularni bitta ustunga kerak bo'lsa, vergul bilan ajratilgan bo'lsa, bu sizning kodingiz:

DECLARE @xml XML=
N'
  
    
  
  
    
    
    
    
  
';
select @xml

SELECT 
    cg.value('(@Usage)[1]', 'varchar(max)')                 as CT1
    ,(  select ', ' + c1.value('(@Name)[1]','varchar(max)') 
        from cg.nodes(N'Column') as C(c1) 
        for xml path(''), type
        ).value('substring(text()[1], 2)', 'varchar(max)')  as CN1
    ,c.value('(@Usage)[1]', 'varchar(max)')                 as CT2
    ,(  select ', ' + c2.value('(@Name)[1]','varchar(max)') 
        from c.nodes(N'Column') as D(c2) 
        for xml path(''), type
        ).value('substring(text()[1], 2)', 'varchar(max)')  as CN2
FROM 
    @xml.nodes(N'SampleData/ColumnGroup[1]') as A(cg)
    cross apply @xml.nodes(N'SampleData/ColumnGroup[2]') as B(c)

The result is: Query Result

P.S. So'rovni siz nimaga kerak bo'lsa, mos ravishda tahrirlashdi.

0
qo'shib qo'ydi

Agar ularni bitta ustunga kerak bo'lsa, vergul bilan ajratilgan bo'lsa, bu sizning kodingiz:

DECLARE @xml XML=
N'
  
    
  
  
    
    
    
    
  
';
select @xml

SELECT 
    cg.value('(@Usage)[1]', 'varchar(max)')                 as CT1
    ,(  select ', ' + c1.value('(@Name)[1]','varchar(max)') 
        from cg.nodes(N'Column') as C(c1) 
        for xml path(''), type
        ).value('substring(text()[1], 2)', 'varchar(max)')  as CN1
    ,c.value('(@Usage)[1]', 'varchar(max)')                 as CT2
    ,(  select ', ' + c2.value('(@Name)[1]','varchar(max)') 
        from c.nodes(N'Column') as D(c2) 
        for xml path(''), type
        ).value('substring(text()[1], 2)', 'varchar(max)')  as CN2
FROM 
    @xml.nodes(N'SampleData/ColumnGroup[1]') as A(cg)
    cross apply @xml.nodes(N'SampleData/ColumnGroup[2]') as B(c)

The result is: Query Result

P.S. So'rovni siz nimaga kerak bo'lsa, mos ravishda tahrirlashdi.

0
qo'shib qo'ydi