none
XML query output to File in SQL Server 2000?

    Question

  • Hey All,
      I wanted to build a simple scheduled task that hits my document library table in SQL Server, builds an RSS (XML) file based on that table, and then saves the resulting XML as a flat .rss file.  I'm using SQL Server 2000, and so far I've gotten SQL Server to output the XML from the table in the right format for RSS (using FOR XML EXPLICIT) as a query.  I'm not sure where to go from here - can't figure out how to go from the query output to a flat file.  My idea was to have the whole thing bundled into a stored procedure that I can call for an INSERT trigger on my document library table. 

    Also, I noticed that when I ran the query, the recordset returned the XML split into 2 rows with the column name "XML_F52E2B61-18A1-11D1-B105-00805F49916B".  Seems like it would help if I could store the resulting XML in a variable and then use bcp.exe, but I can't seem to get my FOR XML EXPLICIT command to do anything but spit out the query results onscreen.

    Can anyone point me in the right direction? 

    I appreciate the help!

    Thanks!

    For those of you interested, below is the SQL I used to generate the RSS:

    SELECT 1 as tag,
     null as parent,
     '2.0' as [rss!1!version],
     null as [channel!2!title!element],
     null as [channel!2!link!element],
     null as [channel!2!description!element],
     null as [channel!2!language!element],
     null as [channel!2!pubDate!element],
     null as [channel!2!lastBuildDate!element],
     null as [item!3!title!element],
     null as [item!3!link!element],
     null as [item!3!description!element],
     null as [item!3!pubDate!element],
     null as [item!3!guid!element]
    UNION
    SELECT  2,
     1,
     null,
     'RSS FEED TITLE', 
     'SITE URL',
     'DESCRIPTION',
     'en-us',
     getdate(),
     getdate(),
     null,
     null,
     null,
     null,
     null
    UNION
    SELECT 3,
     2,
     null,
     null,
     null,
     null,
     null,
     null,
     null,
     document_title,
     document_url,
     document_post_date,
     unique_id,
    FROM document_library
    For XML EXPLICIT

    Tuesday, November 01, 2005 8:06 PM

Answers

  • If you are using SQL Server 2000, you should write a small mid-tier app that uses either OLEDB/ADO and their ICommandStream interface or ADO.Net and the ExecuteXMLReader (The following page has
    an snippet code on how to do it:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutexmlreadertopic.asp

    ).
     
    Best regards
    Michael

    Hey All,
      I wanted to build a simple scheduled task that hits my document library table in SQL Server, builds an RSS (XML) file based on that table, and then saves the resulting XML as a flat .rss file.  I'm using SQL Server 2000, and so far I've gotten SQL Server to output the XML from the table in the right format for RSS (using FOR XML EXPLICIT) as a query.  I'm not sure where to go from here - can't figure out how to go from the query output to a flat file.  My idea was to have the whole thing bundled into a stored procedure that I can call for an INSERT trigger on my document library table. 

    Also, I noticed that when I ran the query, the recordset returned the XML split into 2 rows with the column name "XML_F52E2B61-18A1-11D1-B105-00805F49916B".  Seems like it would help if I could store the resulting XML in a variable and then use bcp.exe, but I can't seem to get my FOR XML EXPLICIT command to do anything but spit out the query results onscreen.

    Can anyone point me in the right direction? 

    I appreciate the help!

    Thanks!

    For those of you interested, below is the SQL I used to generate the RSS:

    SELECT 1 as tag,
     null as parent,
     '2.0' as [rss!1!version],
     null as [channel!2!title!element],
     null as [channel!2!link!element],
     null as [channel!2!description!element],
     null as [channel!2!language!element],
     null as [channel!2!pubDate!element],
     null as [channel!2!lastBuildDate!element],
     null as [item!3!title!element],
     null as [item!3!link!element],
     null as [item!3!description!element],
     null as [item!3!pubDate!element],
     null as [item!3!guid!element]
    UNION
    SELECT  2,
     1,
     null,
     'RSS FEED TITLE', 
     'SITE URL',
     'DESCRIPTION',
     'en-us',
     getdate(),
     getdate(),
     null,
     null,
     null,
     null,
     null
    UNION
    SELECT 3,
     2,
     null,
     null,
     null,
     null,
     null,
     null,
     null,
     document_title,
     document_url,
     document_post_date,
     unique_id,
    FROM document_library
    For XML EXPLICIT

    Friday, November 04, 2005 6:57 AM

All replies

  • If you are using SQL Server 2000, you should write a small mid-tier app that uses either OLEDB/ADO and their ICommandStream interface or ADO.Net and the ExecuteXMLReader (The following page has
    an snippet code on how to do it:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutexmlreadertopic.asp

    ).
     
    Best regards
    Michael

    Hey All,
      I wanted to build a simple scheduled task that hits my document library table in SQL Server, builds an RSS (XML) file based on that table, and then saves the resulting XML as a flat .rss file.  I'm using SQL Server 2000, and so far I've gotten SQL Server to output the XML from the table in the right format for RSS (using FOR XML EXPLICIT) as a query.  I'm not sure where to go from here - can't figure out how to go from the query output to a flat file.  My idea was to have the whole thing bundled into a stored procedure that I can call for an INSERT trigger on my document library table. 

    Also, I noticed that when I ran the query, the recordset returned the XML split into 2 rows with the column name "XML_F52E2B61-18A1-11D1-B105-00805F49916B".  Seems like it would help if I could store the resulting XML in a variable and then use bcp.exe, but I can't seem to get my FOR XML EXPLICIT command to do anything but spit out the query results onscreen.

    Can anyone point me in the right direction? 

    I appreciate the help!

    Thanks!

    For those of you interested, below is the SQL I used to generate the RSS:

    SELECT 1 as tag,
     null as parent,
     '2.0' as [rss!1!version],
     null as [channel!2!title!element],
     null as [channel!2!link!element],
     null as [channel!2!description!element],
     null as [channel!2!language!element],
     null as [channel!2!pubDate!element],
     null as [channel!2!lastBuildDate!element],
     null as [item!3!title!element],
     null as [item!3!link!element],
     null as [item!3!description!element],
     null as [item!3!pubDate!element],
     null as [item!3!guid!element]
    UNION
    SELECT  2,
     1,
     null,
     'RSS FEED TITLE', 
     'SITE URL',
     'DESCRIPTION',
     'en-us',
     getdate(),
     getdate(),
     null,
     null,
     null,
     null,
     null
    UNION
    SELECT 3,
     2,
     null,
     null,
     null,
     null,
     null,
     null,
     null,
     document_title,
     document_url,
     document_post_date,
     unique_id,
    FROM document_library
    For XML EXPLICIT

    Friday, November 04, 2005 6:57 AM
  • On a slightly unrelated note: Make sure that you use an order by clause that provides the right grouping of the rows that describe the parent and children nodes. A parent has to be directly followed by its children. You do this by

    1. repeating the parent key value on the rows for the children
    2. use them in the order by

    In your example query above, you are not doing this which will lead to the wrong XML result.

    Best regards
    Michael
    Friday, November 04, 2005 7:56 PM
  • Thanks for all the helpful comments!  Unfortunately, due to time restrictions I wound up just building a web application tool that builds the RSS file when the document edit form is submitted.

    But thanks again!
    Friday, November 04, 2005 8:36 PM