Can FOR XML EXPLICIT handle item lists within Child tables?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Mary Ellen (Ellen_at_discussions.microsoft.com)
Date: 10/14/04

Next message: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Previous message: Barry Tang: "SQLXMLBulkLoad for Oracle"
Next in thread: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Reply: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Messages sorted by: [ date ] [ thread ]

Date: Thu, 14 Oct 2004 15:15:06 -0700

I have been working with a FOR XML Explicit SQL statement for days trying to
get my multiple child tables to nest inside my parent table correctly.
I cannot get the child items to list inside their parent list tag correctly.

I want
- <customer>
  <salute>Mr.</salute>
  <firstname>John</firstname>
  <middleinit>G.</middleinit>
  <lastname>Smith</lastname>
  <jobtitle>DMTS</jobtitle>
  <deptment>Dept 2338</deptment>
  <company>United States Air Force</company>
  <address1>PO Box 5800</address1>
  <city>Washington</city>
  <state>DC</state>
  <postalcode>02220-0100</postalcode>
  <country>US</country>
  <email>jgsmith@usaf.gov</email>
  <phonearea>207</phonearea>
  <phonemain>844-8371</phonemain>
  <prefill>1100</prefill>
  <fillflag>1</fillflag>
  <datereceived>10/12/2004</datereceived>
  <doctype>Web Form</doctype>
  <notes>[[ event code = AADDD ]][[ Form Page URL =
/test/cl/req.html?respFormData=35 ]]</notes>
  <ffmtype>Normal</ffmtype>
  <tracenum>65</tracenum>
- <productlist>
        <product>123</product>
        <product>345</product>
</productlist>
</customer>

instead I get

- <customer>
  <salute>Mr.</salute>
  <firstname>John</firstname>
  <middleinit>G.</middleinit>
  <lastname>Smith</lastname>
  <jobtitle>DMTS</jobtitle>
  <deptment>Dept 2338</deptment>
  <company>United States Air Force</company>
  <address1>PO Box 5800</address1>
  <city>Washington</city>
  <state>DC</state>
  <postalcode>02220-0100</postalcode>
  <country>US</country>
  <email>jgsmith@usaf.gov</email>
  <phonearea>207</phonearea>
  <phonemain>844-8371</phonemain>
  <prefill>1100</prefill>
  <fillflag>1</fillflag>
  <datereceived>10/12/2004</datereceived>
  <doctype>Web Form</doctype>
  <notes>[[ event code = AADDD ]][[ Form Page URL =
/test/cl/req.html?respFormData=35 ]]</notes>
  <ffmtype>Normal</ffmtype>
  <tracenum>65</tracenum>
  <productlist />
- <productlist>
- <productlist>
  <product>123</product>
  </productlist>
- <productlist>
  <product>345</product>
  </productlist>
  </productlist>
  </customer>

</customer>

My select statement looks like:

SELECT 1 as TAG, 0 as parent,
        -- customer.
        AccessID as [customer!1!accessid!element],
        Salutation as [customer!1!salute!element],
        FirstName as [customer!1!firstname!element],
        MiddleName as [customer!1!middleinit!element],
        LastName as [customer!1!lastname!element],
        Suffix as [customer!1!suffix!element],
        JobTitle as [customer!1!jobtitle!element],
        Department as [customer!1!deptment!element],
        Company as [customer!1!company!element],
        Address1 as [customer!1!address1!element],
        Address2 as [customer!1!address2!element],
        MailStop as [customer!1!mailstop!element],
        City as [customer!1!city!element],
        County as [customer!1!county!element],
        State as [customer!1!state!element],
        PostalCode as [customer!1!postalcode!element],
        PostalCode4 as [customer!1!postcode4!element],
        Country as [customer!1!country!element],
        Email as [customer!1!email!element],
        PhoneCountryCode as [customer!1!phonecode!element],
        PhoneAreaCode as [customer!1!phonearea!element],
        PhoneMainNumber as [customer!1!phonemain!element],
        PhoneExtension as [customer!1!phoneext!element],
        EventCode as [customer!1!eventid!element],
        isnull(Prefulfillment,'0000') as [customer!1!prefill!element],
        isnull(PreFulfillflag, '0') as [customer!1!fillflag!element],
        convert(char(12), DateReceived, 101) as [customer!1!datereceived!element],
        GraduationYear as [customer!1!gradyear!element],
        Degree as [customer!1!degree!element],
        DocTypeCode as [customer!1!doctype!element],
        EnteredBy as [customer!1!enteredby!element],
        Notes as [customer!1!notes!element],
        Build as [customer!1!build!element],
        FFMType as [customer!1!ffmtype!element],
-- ffmprodlist with ffmprod inside
        NULL as [productlist!2!webformsubmitid!hide],
        NULL as [productlist!3!product!element],
        WEB_FORM_SUBMIT.webformsubmitid as [customer!1!tracenum!element]
FROM WEB_FORM_SUBMIT

UNION ALL
        select 2,1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        web_form_productlist.webformsubmitid,
        NULL, ----web_form_productlist.productid,
        WEB_FORM_SUBMIT.webformsubmitid
from web_form_productlist, WEB_FORM_SUBMIT where
web_form_productlist.webformsubmitid = WEB_FORM_SUBMIT.webformsubmitid

UNION ALL
        select 3,2,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        web_form_productlist.webformsubmitid,
        web_form_productlist.productid,
        WEB_FORM_SUBMIT.webformsubmitid
from web_form_productlist,
WEB_FORM_SUBMIT
where web_form_productlist.webformsubmitid = WEB_FORM_SUBMIT.webformsubmitid
ORDER BY [customer!1!tracenum!element], [productlist!3!product!element],
[productlist!2!webformsubmitid!hide], TAG
FOR XML EXPLICIT

Have I hit a limitation of the FOR XML EXPLICIT command, do I just have some
part of the SQL incorrect?

I also need my empty tags to show up, is that possible?
Thanks,

Next message: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Previous message: Barry Tang: "SQLXMLBulkLoad for Oracle"
Next in thread: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Reply: Bertan ARI [MSFT]: "Re: Can FOR XML EXPLICIT handle item lists within Child tables?"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: MS SQL Server XML Query Help
... children element rows are ordered directly after their parent element rows. ... you order based on the tag. ... which should give you a correct XML result when applying FOR XML EXPLICIT. ... Sometimes the order may work under one query plan but not when the ...
(microsoft.public.sqlserver.xml)
Re: FOR XML EXPLICIT issue
... NULL AS Parent, ... UNION ALL ... SELECT 2 AS Tag, ... FOR XML EXPLICIT ...
(microsoft.public.sqlserver.xml)
Re: Help with for xml explicit
... clause so that the order by sorts in the right order. ... the last parent. ... > SELECT 1 AS Tag, ... > FOR XML EXPLICIT ...
(microsoft.public.sqlserver.xml)
Help on XML Explicit
... I am starting to work with XML Explicit. ... Declare @IdCat int ... AS TAG, ... NULL AS PARENT, ...
(microsoft.public.dotnet.xml)
Re: Challenge: nested FOR XML EXPLICIT query with data not in target t
... AS Tag ... ,NULL AS Parent ... FROM vConsolidationPrinting ... UNION ALL ...
(microsoft.public.sqlserver.xml)