Search code examples
sql-serverfor-xml-explicit

FOR XML EXPLICIT: Create multiple result rows

I want to create multiple XML Files/Result Rows in one statement.

What I have:

with invoices as (
    select '1' HID, 'Test1' HData, '1' PID, 'Pos1' PData union
    select '1' HID, 'Test1' HData, '2' PID, 'Pos2' PData union
    select '1' HID, 'Test1' HData, '3' PID, 'Pos3' PData union
    select '2' HID, 'Test2' HData, '4' PID, 'Pos1' PData union
    select '2' HID, 'Test2' HData, '5' PID, 'Pos2' PData union
    select '2' HID, 'Test2' HData, '6' PID, 'Pos3' PData
)
select
    case when GROUPING_ID(PID) = 0 then 4
        when GROUPING_ID(HData) = 0 then 3
        when GROUPING_ID(HID) = 0 then 2
        else 1 end tag,
    case when GROUPING_ID(PID) = 0 then 2
        when GROUPING_ID(HData) = 0 then 2
        when GROUPING_ID(HID) = 0 then 1
        else null end parent,
    null [Root!1],
    HID [Invoice!2!HID],
    HData [Header!3!HData!Element],
    PID [Pos!4!PID],
    PData [Pos!4!PData!Element]
from invoices
group by grouping sets ((), (HID), (HID, HData), (HID, HData, PID, PData))
order by HID, HData, PID
for xml explicit, type;

What I get: get

What I want: want

Is this possible? How would one do that?

Solution

  • You simply need to generate the XML output for each distinct HID:

    ;WITH invoices as (
    select '1' HID, 'Test1' HData, '1' PID, 'Pos1' PData union
    select '1' HID, 'Test1' HData, '2' PID, 'Pos2' PData union
    select '1' HID, 'Test1' HData, '3' PID, 'Pos3' PData union
    select '2' HID, 'Test2' HData, '4' PID, 'Pos1' PData union
    select '2' HID, 'Test2' HData, '5' PID, 'Pos2' PData union
    select '2' HID, 'Test2' HData, '6' PID, 'Pos3' PData
)
SELECT 
   XMLColumn = (
      select
         case when GROUPING_ID(PID) = 0 then 4
              when GROUPING_ID(HData) = 0 then 3
              when GROUPING_ID(HID) = 0 then 2
              else 1 
         end tag,
         case when GROUPING_ID(PID) = 0 then 2
              when GROUPING_ID(HData) = 0 then 2
              when GROUPING_ID(HID) = 0 then 1
              else null 
         end parent,
         null [Root!1],
         HID [Invoice!2!HID],
         HData [Header!3!HData!Element],
         PID [Pos!4!PID],
         PData [Pos!4!PData!Element]
      from invoices
      WHERE i.HID = HID
      group by grouping sets ((), (HID), (HID, HData), (HID, HData, PID, PData))
      order by HID, HData, PID
      for xml explicit, type
   )
FROM invoices i
GROUP BY HID