Extracting data from SQL -> XML

[Stu20001]

Hi - I'm a newbie to this stuff so would appreciate any sugestions

I have a SQL database driven news site that I want to setup an automatic XML feed to another site from. What I need to know is how can I produce an XML file containing the required data from the database automatically on a scheduled basis (e.g. every Sunday at midnight, a process runs that extracts the data, creates the xml file and saves it to a certain directory).

Any help appreciated!

 

[Ladyhawk]

You can use the RAW mode (or similar mode) when you write the SQL query.

RAW mode transforms each row in the query result set into an XML element with the generic identifier row. Each column value that is not NULL is mapped to an attribute of the XML element in which the attribute name is the same as the column name.

This query returns customer and order information. RAW mode is specified in the FOR XML clause.

SELECT Customers.CustomerID, Orders.OrderID, Orders.OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW

This is the partial result:

<row CustomerID=&quot;ALFKI&quot; OrderID=&quot;10643&quot; OrderDate=&quot;1997-08-25T00:00:00&quot;/>
<row CustomerID=&quot;ANATR&quot; OrderID=&quot;10308&quot; OrderDate=&quot;1996-09-18T00:00:00&quot;/>
<row CustomerID=&quot;ANATR&quot; OrderID=&quot;10625&quot; OrderDate=&quot;1997-08-08T00:00:00&quot;/>
<row CustomerID=&quot;AROUT&quot; OrderID=&quot;10355&quot; OrderDate=&quot;1996-11-15T00:00:00&quot;/>

This was all out of the SQL Server Books Online.

Cheers,

Ladyhawk.
** ASP/VB/Java Programmer **

 

[palbano]

Ladyhawk
>> This was all out of the SQL Server Books Online.

Yeah... why do they always hide those facts in the documentation? What... they think people read that stuff!


-pete