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.
>

FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!

Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.

<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...

</Customer>
</Customers>

Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!

My apologies for the delay in answering....

If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...

Here is the EXPLICIT solution:

select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"

union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers

-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit

and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):

select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')

Best regards
Michael