How to export SQL Server 2000 data in

kwilliams · November 21st, 2005, 04:02 PM

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.

KWilliams

pgtips · November 22nd, 2005, 05:35 AM

I don't think you'll find any more comprehensive article than this
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp

kwilliams · November 22nd, 2005, 10:38 AM

Hi pgtips,

I reviewed that article, especially the "Using SQL Server 2000 XML Features" section at http://www.perfectxml.com/Articles/X...rtSQLXML.asp#4.

It all seemed to work correctly...I even received this message from SQL Query Analyzer:
The command(s) completed successfully.

...but no new XML file was created. Any suggestions.

KWilliams

pgtips · November 22nd, 2005, 11:26 AM

most likely a permissions problem creating the file:

Quote:

quote:
Permissions
The user must have SELECT permissions to run the specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only the members of the sysadmin fixed server role can impersonate other users.

kwilliams · November 22nd, 2005, 11:38 AM

Well, unbelievably I realized that I had had clicked the "Parse" button and not the "Execute" button. When I did click the proper button, I received this error message:
Server: Msg 16822, Level 11, State 1, Procedure sp_makewebtask, Line 125
SQL Web Assistant: Could not open the template file.

When I changed the b:\sample.txt and b:\sample.xml references to http://SERVERNAME/DIRECTORY/sample.txt and http://SERVERNAME/DIRECTORY/sample.xml, QA just locked up and continued to say "Executing query batch..." without a conclusion. Of course "http://SERVERNAME/DIRECTORY/" is substituted with the actual servername and directory on my side, but I changed it on this post for security reasons.

So do you have any suggestions on why it wouldn't be able to find that file? I did just as the tutorial told me to do. Thanks again for your help.

KWilliams

kwilliams · November 22nd, 2005, 06:25 PM

Hello again,

Well, I've made a lot of progress from this morning. I created the following Stored Procedure from the tutorial that you forwarded me earlier:
CREATE PROCEDURE [dbo].[spsample] AS
SELECT * FROM sample FOR XML AUTO,ELEMENTS
GO

and then I executed the SP from Query Analyzer. The data filled the XML tags exactly how I'd like. Now I just need to save the SP's results to an XML file on the same server within a <root> tag. I'm just not sure how I'm supposed to add this code to my SP. Have you worked with this method before? If so, could you let me know of the best way to add it to my existing SP, or point me in the right direction? Thanks for any help.

KWilliams

pgtips · November 23rd, 2005, 06:12 AM

I haven't used sp_makewebtask before, but I have used "for xml" queries a lot.

As I said before, you will need to sort out permissions before you can create/read files from within SQL Server. The main issue is that sp_makewebtask runs as a job, so it does not run with the permissions of your user account, instead it's whatever user the SQL Server service runs under (often this is the local service account - you can find out which for your server by right-clicking the server in EM, choosing Properties > Security and looking at the "startup service account"). Once you have given permissions to the SQL Server account to read/write to the folders you want to use then you can re-try the sp_makewebtask. (BTW the file names should be in the form of drive:\\folder\file NOT http://...)

hth
Phil

kwilliams · November 28th, 2005, 11:30 AM

If I wanted to go about this process a different way, like this:

I understand the syntax for executing a SP from an XML file:

Code:

<root xmlns:sql="urn:schemas=microsoft-com:xml-sql">
    <sql:query>
    EXEC spSample    
    </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.

KWilliams

pgtips · November 28th, 2005, 12:20 PM

either you're running the sql through something like an ADO command object, in which case that knows the connection details,
or you're accessing the query template over HTTP so the full url points to a special IIS virtual directory which was set up using SQL XML IIS and that vdir knows how to connect

have you looked at this stuff in BOL?

kwilliams · November 28th, 2005, 01:58 PM

...but how is a Stored Procedure going to work when it's executed from an XML doc without some sort of connection? If I have an XML file at:
B:\newsite\sample.xml, and the server is running SQL Server, how would I then connect the XML Template with the SP on the DB?

For instance, in ASP I'd use:

Code:

<%@language="javascript"%>

<%
var rsAppLogin = Server.CreateObject("ADODB.Recordset");
rsAppLogin.ActiveConnection = MM_strConn_STRING;
rsAppLogin.Source = "SELECT *  FROM dbo.Sample";
rsAppLogin.CursorType = 0;
rsAppLogin.CursorLocation = 2;
rsAppLogin.LockType = 3;
rsAppLogin.Open();
var rsAppLogin_numRows = 0;
%>

What's the equivalent in XML? And if there is none, then how does the "EXEC spSample" know where to get the data from? Thanks.

KWilliams