I have XML data from which I would like only the values concatenated together per key value. The below code comes close but does not work, please advise. What I tried below is to first "split" the values out of the XML, then reconcatenate them together, I am hoping there is a better way, else just a correction of my code will be appreciated.
Just a note: I struggle to wrap my head around how XML is implemented in SQL so the answer may be obvious
SELECT RoleId, /*This part does not work*/ STUFF((SELECT ', ' + Condition FROM filters /*Invalid object name 'filters'.*/ FOR XML PATH ('')), 1,1, '') vals /*This part does not work*/ FROM ( /*This part works*/ SELECT tbl.RoleId, p.value('@Condition', 'VARCHAR(8000)') AS Condition FROM ( SELECT RoleId, r.RoleName, CAST(Data AS XML) Data FROM dbo.RoleFilters rf INNER JOIN dbo.Roles r ON r.Id = rf.RoleId ) tbl CROSS APPLY Data.nodes('/RoleFilters/Filters/ExpressionInfoGroup/Filters/Expression') t(p) ) filters
No comments:
Post a Comment