FOR XML EXPLICIT Problem

[Skittle]

I am investigating the FOR XML EXPLICIT option of the SELECT statement to see if it is a better option for describing the way data is output in XML.

In my example below I get an error message I don't understand.


Server: Msg 6833, Level 16, State 1, Line 1
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.



I'm trying to create an XML file with three levels:-

<REGIONS SalesArea=’A1’>
	
      <ORDER ORDERID=’000001’ CUSTOMER=’123456’>
            <LINES POS=’001’ ITEM=’0000000803232 ’/>
            <LINES POS=’002’ ITEM=’0000000803234 ‘/>
            <LINES POS=’003’ ITEM=’0000000803235 ‘/>
      </ORDER>

      <ORDER ORDERID=’000002’ CUSTOMER=’123456’>
            <LINES POS=’001’ ITEM=’0000000803234 ‘/>
      </ORDER>

</REGIONS>

I have two tables to get the information from.
The ORDERHEADER table and the ORDERDETAIL table.

SELECT 1 as Tag,
 NULL As Parent,
             OHSALA AS [REGIONS!1!SALESAREA],
             NULL   AS [ORDER!2!ORDERID],
             NULL   AS [ORDER!2!CUSTOMER],
             NULL   AS [LINES!3!POS],
             NULL   AS [LINES!3!ITEM]
FROM ORDERHEADER H

UNION ALL

SELECT 2,
       1,
             NULL,
             H.OHORDN,
             H.OHCUST,
             NULL,
             NULL
FROM  ORDERHEADER H

UNION ALL

SELECT 3,
       2,
             NULL,
             H.OHORDN,
             NULL,
             D.ODPOSN,
             D.ODITEM
FROM   ORDERHEADER H 
       INNER JOIN ORDERDETAIL D ON H.OHORDN = D.ODORDN

ORDER BY [REGIONS!1!SALESAREA],
         [ORDER!2!ORDERID],
         [LINES!3!POS]
FOR XML EXPLICIT

Can anybody see where I am going wrong?

Dazed and confused
(N+, MCAD)

 

[Skittle]

Revisited this last night ( couldn't sleep ).

The reason it didn't work was because I had not included
H.OHSALA in the level 2 and level 3 column list. I had it as NULL. If a column is used for a condition or on an order at a level, it needs to be selected and not left as null.

SELECT 1 as Tag,
 NULL As Parent,
             OHSALA AS [REGIONS!1!SALESAREA],
             NULL   AS [ORDER!2!ORDERID],
             NULL   AS [ORDER!2!CUSTOMER],
             NULL   AS [LINES!3!POS],
             NULL   AS [LINES!3!ITEM]
FROM ORDERHEADER H

UNION ALL

SELECT 2,
       1,
             H.OHSALA,
             H.OHORDN,
             H.OHCUST,
             NULL,
             NULL
FROM  ORDERHEADER H

UNION ALL

SELECT 3,
       2,
             H.OHSALA,
             H.OHORDN,
             NULL,
             D.ODPOSN,
             D.ODITEM
FROM   ORDERHEADER H 
       INNER JOIN ORDERDETAIL D ON H.OHORDN = D.ODORDN

ORDER BY [REGIONS!1!SALESAREA],
         [ORDER!2!ORDERID],
         [LINES!3!POS]
FOR XML EXPLICIT

Dazed and confused
(N+, MCAD)