FOR XML EXPLICIT Question

I am trying to bcp data from the Customers table in the Northwind database
using the following format:
<Customer>
<CustomerID>ALFKI</CustomerID>
<Company>Alfreds Futterkiste</Company>
</Customer>
<Contact>
<Name>Maria Anders</Name>
<Title>Sales Representative</Title>
<Contact>
The query will always return one record at a time, but needs to be split
with the Customer tag and the Contact tag. My query is the following:
select 1 as Tag, NULL as Parent,
CustomerID AS [Customer!1!CustomerID!element],
CompanyName AS [Customer!1!Company!element],
ContactName AS [Contact!2!Name!element],
ContactTitle AS [Contact!2!Title!element]
FROM Customers WHERE PostalCode = '12209'
UNION ALL
select 2 as Tag, NULL as Parent,
CustomerID AS [Customer!1!CustomerID!element],
CompanyName AS [Customer!1!Company!element],
ContactName AS [Contact!2!Name!element],
ContactTitle AS [Contact!2!Title!element]
FROM Customers WHERE PostalCode = '12209'
FOR XML EXPLICIT
If I alter my select statement to just run the first select and use bcp to
export it, I can view the results in the XML format. But when I run the
complete statement and use bcp, when I view the document, the format appears
in non-XML format (basically, have to edit with Notpad, as opposed to
viewing it with Internet Explorer). Is there something wrong with my
statement?
TIA
Darin H.
The XML document format requires that you have a single top-level element
node. Your example data (and your query) generates what is commonly called a
fragment (ie more than one top-level element).
You can either use the root property of the SQLOLEDB provider (or ADO), use
a SQLXML template (again via the providers) or use another selection in our
FOR XML EXPLICIT query to add a root around the result.
Feel free to check out some of the FOR XML postings on my blog at
http://sqljunkies.com/weblog/mrys (or the FOR XML whitepaper link also
provided there) for seeing such an EXPLICIT mode query explained (and how
SQL Server 2005 will make it simpler).
Regarding your query below: probably should add an "order by
[Customer!1!CustomerID!element]" and you can leave the Contactname and title
in the first and the CompanyName in the second selection as NULL.
HTH
Michael
"Darin Hobbs" <darinh@.callow.ca> wrote in message
news:%237W$Kh6jEHA.1904@.TK2MSFTNGP09.phx.gbl...
>I am trying to bcp data from the Customers table in the Northwind database
> using the following format:
> <Customer>
> <CustomerID>ALFKI</CustomerID>
> <Company>Alfreds Futterkiste</Company>
> </Customer>
> <Contact>
> <Name>Maria Anders</Name>
> <Title>Sales Representative</Title>
> <Contact>
> The query will always return one record at a time, but needs to be split
> with the Customer tag and the Contact tag. My query is the following:
> select 1 as Tag, NULL as Parent,
> CustomerID AS [Customer!1!CustomerID!element],
> CompanyName AS [Customer!1!Company!element],
> ContactName AS [Contact!2!Name!element],
> ContactTitle AS [Contact!2!Title!element]
> FROM Customers WHERE PostalCode = '12209'
> UNION ALL
> select 2 as Tag, NULL as Parent,
> CustomerID AS [Customer!1!CustomerID!element],
> CompanyName AS [Customer!1!Company!element],
> ContactName AS [Contact!2!Name!element],
> ContactTitle AS [Contact!2!Title!element]
> FROM Customers WHERE PostalCode = '12209'
> FOR XML EXPLICIT
> If I alter my select statement to just run the first select and use bcp to
> export it, I can view the results in the XML format. But when I run the
> complete statement and use bcp, when I view the document, the format
> appears
> in non-XML format (basically, have to edit with Notpad, as opposed to
> viewing it with Internet Explorer). Is there something wrong with my
> statement?
> TIA
> Darin H.
>