SQL Query to XML File

[plyon70]

I have an issue and I am hoping some people can help me out. I need to export certain information from SQL to an XML file for FTP to be then imported into another SQL database. I found some documentation on the web but it does not seem to be right.

My data is not complete and I can tell I am missing some of it. Below is both the SQL query where I get all the right data and then below that is the beginning of the XML query I started. Keep in mind I have not added all the fields yet to the XML b/c I can't even get the first few fields to export correctly.

SELECT dbo.sitename.s_site_name,
dbo.transactions.i_ticket_id,
dbo.transactions.c_amount as 'Ticket Total',
dbo.transactions.s_credit_auth as 'Room Number',
dbo.transactions.dt_when
FROM dbo.transactions,
dbo.sitename
WHERE dbo.transactions.s_credit_tran_type = 'Room Charge'

(XML)
SELECT 1 AS tag,
NULL AS parent,
i_ticket_id AS [data!1!identifier],
NULL AS [record!2!i_ticket_id!element],
NULL AS [record!2!c_amount!element],
NULL AS [record!2!s_credit_auth!element]
FROM transactions
WHERE s_credit_tran_type = 'room charge'

UNION ALL

SELECT 2 AS tag,
1 AS parent,
i_ticket_id AS [data!1!identifier],
i_ticket_id AS [record!2!element],
c_amount AS [record!2!element],
s_credit_auth AS [record!2!element]
-- s_credit_tran_type AS [record!2!element]
FROM transactions
WHERE s_credit_tran_type = 'room charge'

 

[ProdAir]

have you looked up the FOR XML clause in books online? Is this being pulled out of the server using an application or are you just gonna run the query in query analyzer and save the results in a text file?

 

[plyon70]

This query would be setup on a timed job. I suppose if I needed to I would write an application to do this but was tryingt o avoid that.