How to export SQL Server 2000 data in

pgtips · November 28th, 2005, 04:37 PM

look in BOL index under "XML, accessing SQL Server at URL" or "Executing SQL Queries Using Templates" that will tell you all about it. You will see that the queries can be run via a URL like http://yourserver/yourapp/templates/sample.xml, but you must set up IIS so it knows where the database and server are located.

kwilliams · November 29th, 2005, 12:59 PM

Hi pgtips,

After reviewing that article again, and some thought, this is what I decided to do.

I created a DTS package in SQL Server that contains an ActiveX Script to pull the data from the DB table and export it into an XML file. I'll eventually schedule this package to run every 10 minutes. I think that this idea works best for me, because users will then access XML data without the DB being queried on each user's visit to the page. So it will end up being more efficient and more secure.

Well, this idea worked with sucess this morning, and there's the ActiveX Script that I'm using:

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
    Dim objADORS
    Dim objXMLDoc

    Dim nodeRoot
    Dim nodeTemp
    Dim nodeSample

    'Create ADO and MSXML DOMDocument Objects
    Set objADORS = CreateObject("ADODB.Recordset")
    Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

    'Run the stored procedure and load the Recordset
    objADORS.Open "SELECT * FROM tblSample", _
        "PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DBNAME;" 

    'Prepare the XML Document
    objXMLDoc.loadXML "<root />"
    Set nodeRoot = objXMLDoc.documentElement

    'For each record in the Recordset
    While Not objADORS.EOF
        Set nodeSample = objXMLDoc.createElement("sample")
        nodeRoot.appendChild nodeSample

        Set nodeTemp = objXMLDoc.createElement("id")
        nodeTemp.nodeTypedValue = Trim(objADORS.Fields("id").Value)
        nodeSample.appendChild nodeTemp

        Set nodeTemp = objXMLDoc.createElement("fname")
        nodeTemp.nodeTypedValue = Trim(objADORS.Fields("fname").Value)
        nodeSample.appendChild nodeTemp

        Set nodeTemp = objXMLDoc.createElement("lname")
        nodeTemp.nodeTypedValue = Trim(objADORS.Fields("lname").Value)
        nodeSample.appendChild nodeTemp

        objADORS.moveNext
    Wend

    objADORS.Close
    Set objADORS = Nothing

    'Save the created XML document
    objXMLDoc.Save "B:\sample.xml"

    Main = DTSTaskExecResult_Success
End Function

And this is the XML output:
<root><sample><id>1</id><fname>Jane</fname><lname>Doe</lname></sample><sample><id>2</id><fname>Joe</fname><lname>Schmo</lname></sample><sample><id>3</id><fname>John</fname><lname>Doe</lname></sample></root>

As you can see, the last step will be to add code to format the code with a tabbed format, like this:
<root>
    <sample>
        <id>1</id>
        <fname>Jane</fname>
        <lname>Doe</lname>
    </sample>
    <sample>
        <id>2</id>
        <fname>Joe</fname>
        <lname>Schmo</lname>
    </sample>
    <sample>
        <id>3</id>
        <fname>John</fname>
        <lname>Doe</lname>
    </sample>
</root>

I'll keep looking into it, but if you know of how to do this, I'd love to know. Thanks for all of your help on this subject...it's greatly appreciated.

KWilliams

pgtips · November 30th, 2005, 08:17 AM

Hi there,
glad you've got it working, but frankly I'm amazed that after reading all that stuff you would go with a "hard work" solution like this. There's nothing wrong with using an ActiveX task, but why build the XML yourself record by record? why not at least use a FOR XML query?

anyway if you want to pretty-print the xml output file you could just add an xslt step (look at TransformNode or TransformNodeToObject function of DOM Document). Here are some options for what the xslt should contain
http://www.dpawson.co.uk/xsl/sect2/pretty.html

hth
Phil

kwilliams · November 30th, 2005, 10:20 AM

Quote:

quote:glad you've got it working, but frankly I'm amazed that after reading all that stuff you would go with a "hard work" solution like this. There's nothing wrong with using an ActiveX task, but why build the XML yourself record by record? why not at least use a FOR XML query?

Yeah, I agree. I had thought about the downsides to doing it on the XML page vs. doing it in SQL Server, and I came to the conclusion that it would likely be more efficient for us and the user if the data was already populated in the XML file from a scheduled SQL Server DTS Package. That way, the Stored Procedure wouldn't have to be run on each user's visit to the page, which I thought could eventually be taxing on the server.

I had also created a working FOR XML select statement within a SP, and was able to get it to run great in Query Analyzer, but couldn't figure out how to export that data into an XML doc. I did mess around with the queryout method quite a bit, but I kept receiving syntax errors. Also, when I tried to create a package and simply use the SP for the exported data, it worked...but all of this empty code appeared in the XML doc.

Quote:

quote:anyway if you want to pretty-print the xml output file you could just add an xslt step (look at TransformNode or TransformNodeToObject function of DOM Document). Here are some options for what the xslt should contain
http://www.dpawson.co.uk/xsl/sect2/pretty.html

That's what I was thinking after my last post. I wish that there was a way to specify that within the ActiveX Script, but this might work in the meantime.

Please let me know if you agree that the process that I've created is more efficient for us and the user. I think that it is, but I always appreciate other opinions. I just thought that it's better for SQL Server to control when that data is exported, and in-turn would not result in 500 users trying to query the DB table at once by visiting that page. Thanks again for all of your help.

KWilliams