www.webdeveloper.com
Results 1 to 5 of 5

Thread: How to export SQL Server 2000 data into XML file

  1. #1
    Join Date
    May 2003
    Posts
    599

    Question How to export SQL Server 2000 data into XML file

    I'm a complete newbie to this idea, but this is what I'd like to do.

    I want to export data from SQL Server 2000 DB table into an already existing XML file. I'd like to do so using a Stored Procedure, which I would then add to a scheduled job.

    I've been messing with the built-in Data Transformation jobs within SQL Server 2000 with some success. I'm able to export table data into an XML file like this:
    Code:
    CREATE TABLE B:\sample.xml (
    id integer (12) NOT NULL, 
    fname varchar (10) NULL, 
    lname varchar (10) NULL
    )
    The output looks like this:
    Code:
    1	Joe	Schmo
    2	John	Doe
    This is the output that I'd like to end up with:
    Code:
    <root>
    	
    	<sample>
    		<id>1</id>
    		<fname>Joe</fname>
    		<lname>Schmo</lname>
    	</sample>
    	
    	<sample>
    		<id>2</id>
    		<fname>John</fname>
    		<lname>Doe</lname>
    	</sample>
    	
    </root>
    or this:
    Code:
    <root>
    	
    	<sample id="1">
    		<fname>Joe</fname>
    		<lname>Schmo</lname>
    	</sample>
    
    	<sample id="2">
    		<fname>John</fname>
    		<lname>Doe</lname>
    	</sample>
    	
    </root>
    Any information or resources would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Sep 2004
    Location
    Northeast, FL
    Posts
    332

  3. #3
    Join Date
    May 2003
    Posts
    599
    Ok, thanks for the info. I've discovered the use of SQL XML Templates, and that's the direction I'm heading in. I've created a Stored Procedure (SP) that has the following query:
    Code:
    CREATE PROCEDURE [dbo].[spsample] AS
    SELECT * FROM sample FOR XML AUTO,ELEMENTS
    GO
    ...and it works great. This is the output:
    <sample>
    <id>
    <fname>
    <lname>
    </sample>
    <sample>...</sample>

    Now I want to execute that SP from an XML file using the XML Template method, like this:
    Code:
    <root xmlns:sql="urn:schemas=microsoft-com:xml-sql">
    [t]<sql:query>
    [t]EXEC spSample[t]
    [t]</sql:query>
    </root>
    ...but I'm really confused on something that probably has a simple solution. How does the XML file know how to connect to the SP? How do I include the connection info (i.e. database, username, password, etc.) for that specific SP within the XML file? Every tutorial I've read so far only shows me how to execute the SP from XML, but it doesn't explain how the connection process works. Do you know how to do this? Or do you know of one or more resources that explain how to do this? Thanks for your help.

  4. #4
    Join Date
    Dec 2011
    Posts
    1

    Automated service

    If you need to export on a frequent base to xml, you can use this tool: http://www.thewebshopstore.com/tools/db2xml/

    It support scheduled exports of result sets of any sql query you configure.

  5. #5
    Join Date
    Nov 2002
    Posts
    2,632
    You might want to look at the age of a thread before posting in it. This thread is over 6 years old.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles