CODEWITHSUNDEEP
sql-serverfor-xml-explicit
JonasK
JonasK

Reputation: 228

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?

Upvotes: 0

Views: 388

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 1

Related Questions