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