How can I extract XML from SQL using DTS?

Friday, November 20, 2009

How can I extract XML from SQL using DTS? (58546 Requests)

Using the DTS ActiveX Script Task it is pretty easy to accomplish something like this. Here is a script that I used to create a Package which was scheduled and executed successfully.

 
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

    Dim oCmd, sSQL, oDom

    ''' If MSXML 4.0 is not installed this will not work!
    Set oDom = CreateObject("Msxml2.DOMDocument.4.0")

    Set oCmd = CreateObject("ADODB.Command")
    oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=db;UID=sa;Password=xyz"

    sSQL = "<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>" & _
           "select * from table for xml auto</sql:query></ROOT>"

    oCmd.CommandText = sSQL
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    oCmd.Properties("Output Stream") = oDom
    oCmd.Execute , , 1024

    oDom.Save "d:\temp\testdts.xml"

    Main = DTSTaskExecResult_Success

End Function

It is pretty easy to add this. Just create a new package, add an ActiveX Script Task, paste in the above script (filling in your own values), schedule and run it.

Feedback

# XML output From DTS 8/22/2001 9:59 AM Jeb Bushell

It should be an option on one or more of the stock tasks -- and it is. But it doesn't work.

# XML output From DTS 8/22/2001 11:11 AM Bryant

What should be an option?

# XML output From DTS 8/23/2001 12:22 PM jeb Bushell

This is how it "should" work: You set up a data pump with a "FOR XML" query as the source. The datapump appears to recognize that something special is going on (check out the transformation tab) and it attempts to output to the given destination file. Bit, the output you get is nonesense numeric, probably hex, and not at all what you'd expect. It will only take you 10 mins to see for yourself.

# XML output From DTS 8/23/2001 12:48 PM jeb Bushell

I think there may be a problem with your script in some circumstances. Here is the relevant change that worked for me: sSQL = "" sSQL = sSQL + "select * from table for xml auto, elements" sSQL = sSQL + "" oCmd.Command

# XML output From DTS 8/23/2001 12:58 PM Jeb Bushell

That got screwed up somehow. Try this: sSQL = "" sSQL = sSQL + "select * from plusfund_extract for xml auto, elements" sSQL = sSQL + "" oCmd.Command

# XML output From DTS 8/24/2001 7:36 AM Jeb Bushell

I sent the unscrewed script via e-mail. Can the oDom.Save method accept a variable as an argument? And, if so, how? Mine seems to need a hard-coded string, which makes it clumsy for real world us

# I get an error! 11/3/2001 12:18 AM Dan

When I run the Function Main I get: Error Source: Microsoft Data Transformation Services (DTS) Package Error Description: Error Code:0 Error Source=Microsoft VBScript runtime error Error Description: Object required: 'oCom' Error on Line 9 Line 9 is my ActiveConnection string. oCom.ActiveConnection = "Provider=SQLOLEDB;Data Source=netserver;Initial Catalog=PWR;UID=sa;Password="

# I get an error! 11/3/2001 7:49 PM Bryant

I believe we solved this in the newsgroup by changing your variable name.

# error 12/10/2001 11:14 AM francis

curing provider do not accept output stream ???

# error 12/11/2001 5:09 PM Bryant

What is your connection string (minus the username/password). You must use the SQLOLEDB provider to make this work.

# Output file 1/5/2002 7:41 PM Ed

Is it possible to dynamically name the ouptut file via a global parameter ? If so, how? Thanks in advance.

# Almost Works 3/21/2002 2:18 PM Clifford A. Anderson

The code runs in my DTS Package just fine, but it outputs a 0-byte file. Any ideas why this might be happening? Tnx1.0e6.

# Almost Works 3/21/2002 4:51 PM Clifford A. Anderson

Having hacked around with it, I found that Jeb's solution below in "XML output From DTS" also solved my problem... put the namespace reference in the root node and put sql:query tags below it - this works great.

# Missing = between element/Attr 4/17/2002 8:20 PM Terry Brown

Gives following error: MSXML2: Missing eual signs between attribute and attribute value. Please help I think I am close to getting this much need process completed. Thanks in advance. Terry D. Brown terry.brown@exelnal.com '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim oCmd, sSQL, oDom ''' If MSXML 4.0 is not installed this will not work! set oDom = CreateObject("Msxml2.DOMDocument.4.0") set oCmd = CreateObject("ADODB.Command") oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=EBIZ-SQL1;Initial Catalog=mydatabase;User ID=SA;Password=xxxxx;" sSQL = "" sSQL = sSQL + "select * from STATUS_TYPES for xml auto" sSQL = sSQL & "" oCmd.CommandText = sSQL oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" oCmd.Properties("Output Stream") = oDom oCmd.Execute , , 1024 oDom.Save "d:\temp\testdts.xml" Main = DTSTaskExecResult_Success End Function

# XML output From DTS 5/30/2002 9:34 AM wvg

Can someone tell me what "Output Stream" is in the line below! oCmd.Properties("Output Stream") = oDom I'm getting an error message saying that he can't find the object with this name. Do I need to change this ?? and to what Thanks in advance

# Extract XML from SQL using DTS 8/14/2002 8:54 PM Perry Molendijk

I tried this and it worked fine. But it didn't work with "mapping-schema"? Is this possible?

# Works great. 9/26/2002 12:16 PM Bill H.

Works great, I am using " for xml auto,elements" on the SQL statement and not just simply " for xml auto". Many thanks to www.sqlxml.org for posting this, saved me a LOT of work.

# Wonderful! 10/8/2002 9:50 PM yvette

The code works! Thanks

# Output file 11/19/2002 2:09 PM Schem

Yes, this is pretty simple. Go to the properties of the DTS package (not the activeX task) There is a tab called Global. Put your global variable in there. When you go back to the script, it will appear under Global variables in the side bar. Then just put the cursor where you want it and double click the global variable in the side bar.

# extract XML from SQL using DTS 12/6/2002 6:14 AM Olibh

Hi, I have sucessfully extracted a file to xml using code received on this sit (much thanks) Function Main() Dim oCmd, sSQL, oDom ''' If MSXML 4.0 is not installed this will not work! Set oDom = CreateObject("Msxml2.DOMDocument.4.0") Set oCmd = CreateObject("ADODB.Command") oCmd.ActiveConnection = "PROVIDER=SQLOLEDB;SERVER=.;UID=sa;PWD=;DATABASE=Hibernian;" sSQL = "" & _ "execute getxml" oCmd.CommandText = sSQL oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" oCmd.Properties("Output Stream") = oDom oCmd.Execute , , 1024 oDom.Save "c:\comm-response.xml" Main = DTSTaskExecResult_Success End Function My stored Procedure returns the file in the format I want so I dont need to include in the following SQL string, but when i remove it I get an error - any ideas ?? sSQL = "" & _ "execute getxml"

# extract XML from SQL using DTS 3/11/2003 4:21 AM abu

sSQL = "" & _ "execute getxml"

# Will FOR XML EXPLICIT 5/21/2003 2:26 PM Steve Mueller

Will the above ActiveX code work if the sql query includes FOR XML EXPLICIT? Just being lazy but if anyone has tried it and can tell me it did or did not work that would probably save me some time. Thanks

# I get an error! 5/25/2003 7:04 PM Cindy

When I run above script on the sql server 2000,and got error: ActiveX Component can not create object "Msxml2.DOMDocument.4.0".

# SQL 7.0 5/27/2003 10:54 AM Jerome

Is there an equivalent script that will work with SQL 7.0

# sql server does not exist error? 6/4/2003 11:40 AM Irene

Hi, I am tying to run the script but getting the error message: "SQL Server does not exist or access denied, invalid connection string attribute" The following is my script: oCmd.ActiveConnection = "Provider = SQLOLEDB; Server = SQLTEST; UID = xie ; PWD=greenbean ; Database = shoehorn; " The sever is a remote server called SQLTEST and I have a username called xie and the password is greenbean. It does exist and the username and password is correct. Can anybody tell me what is my problem? Thank you.

# I get an error! 6/4/2003 11:42 AM Irene

Cindy, You need to download the XML 4.0 from the microsoft website. http://www.microsoft.com/downloads/details.aspx?FamilyID=3144b72b-b4f2-46da-b4b6-c5d7485f2b42&displaylang=en Irene

# sql server does not exist error? 6/4/2003 11:52 AM Irene

This is the detail error message: Invalid connectino string attribute Error Code: 0 Error Source = Microsoft OLE DB Provider for SQL server Error Description: Invalid Connection string attribute Error on line 11: [DBNETLIB][Connection Open(Connect()).] SQL Server does not exist or access denied. The following is my script: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim oCmd, sSQL, oDom ''' If MSXML 4.0 is not installed this will not work! Set oDom = CreateObject("Msxml2.DOMDocument.4.0") Set oCmd = CreateObject("ADODB.Command") oCmd.ActiveConnection = "Provider = SQLOLEDB; Server = SQLTEST; UID = sa ; PWD= ; Database = shoehorn; " sSQL = "" & _ "select * from rft< /sql:query>" oCmd.CommandText = sSQL oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" oCmd.Properties("Output Stream") = oDom oCmd.Execute , , 1024 oDom.Save "c:\irene\testdts.xml" Main = DTSTaskExecResult_Success End Function Thank you. Irene

# Whitespace is now allowed error? 6/9/2003 7:10 AM Irene

Hi, I am trying to retrieve the data(XML format) from SQL database using DTS and I am having prblems. The following is the ActiveX script: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim oCmd, sSQL, oDom ''' If MSXML 4.0 is not installed this will not work! Set oDom = CreateObject("Msxml2.DOMDocument.4.0") Set oCmd = CreateObject("ADODB.Command") oCmd.ActiveConnection = "Provider=SQLOLEDB;Server=SQLTEST;database=shoehorn;Userid=xie;password=greenbean;" sSQL = "" & _ "select * from rtf for xml auto, elements< /sql:query>" oCmd.CommandText = sSQL oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" oCmd.Properties("Output Stream") = oDom oCmd.Execute , , 1024 oDom.Save "c:\irene\testdts.xml" Main = DTSTaskExecResult_Success End Function And the error is: Error Code: 0 Error Source = Microsoft XML Extensions to SQL server Error Description: MSXML3: Whitespace is not allowed at this location Error in Line 20 MSXML3: Whitespace is not allowed at this location The line it is complaining is : oCmd.Execute , , 1024 I am totally lost. Can anybody tell me what is wrong? Thank you so much. Irene

# I get an error! 10/7/2003 8:02 AM Warren

When I run above script on the sql server 2000,and got error: Line18 - Item cannot be found in the collection corresponding to the requested name or ordinal. the line18 is " oCmd.Properties("Output Stream") = oDom".

# re: How can I extract XML from SQL using DTS? 10/20/2003 5:22 PM Bryant

See this FAQ:
http://sqlxml.org/faqs.aspx?faq=25

It explains possible reasons for getting errors when setting the output stream.

# re: How can I extract XML from SQL using DTS? 10/20/2003 5:23 PM Bryant

See this FAQ:
http://sqlxml.org/faqs.aspx?faq=25

It explains possible reasons for getting errors when setting the output stream.

# re: How can I extract XML from SQL using DTS? 11/4/2003 6:17 AM albel

what means that magis guid?
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}

# re: How can I extract XML from SQL using DTS? 11/4/2003 6:18 AM albel

what means that magic guid?
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}

# re: How can I extract XML from SQL using DTS? 11/6/2003 9:37 AM GailKnopp

I'm using Oledb and DTS to try to create a file of XML data from MS SQL 2000. What I get is a file that looks like hex. I'm trying to use the DTS Pkg wizard. What options do I need to choose to get the output to be a simple text file?

Thanks....

# re: How can I extract XML from SQL using DTS? 11/6/2003 10:44 AM Bryant

Don't use OLEDB, use SQLOLEDB.

# re: How can I extract XML from SQL using DTS? 11/6/2003 5:17 PM Bryant

The "magic guid" is just to identify the dialect.

# re: How can I extract XML from SQL using DTS? 11/20/2003 1:23 PM Brian

When I run the code, I got the following error.
"Invalid at the top level of the elements." Does anyone have any ideas? Thanks

# re: How can I extract XML from SQL using DTS? 12/1/2003 5:08 PM Bryant

What does the contents of your SQL variable look like? (Make sure you have < and not <)

# re: How can I extract XML from SQL using DTS? 1/6/2004 6:46 AM Kathy

Any way to use a stored procedure in place of the "Select * from table"? This example has worked great for me, except for that now I need to limit my data and when I add "Where datediff(year, birthdate, getdate()) <21" I get an error description of "MSXML3: A name was started with an invalid character." Error Source = "Microsoft XML Extensions to SQL Server". If I take the "Where datediff(year, birthdate, getdate()) <21" out, it works fine. I thought maybe I can subsitute a stored procedure instead but I am getting the error of "Incorrect syntax near the keyword For". Any suggestions would be greatly appreciated! Thanks!!!

# re: How can I extract XML from SQL using DTS? 1/7/2004 6:49 AM Hans Brouwer

I get the following message when parsing the code:

/snip/

Error Description: Expected end of statement
Error on line 15

This is the sSQL = line. Even when I get the 2 lines on the same row this message appears.
What can it be?

TIA,
Hans Brouwer

# re: How can I extract XML from SQL using DTS? 1/7/2004 8:38 AM Kathy

Hans,
Make sure you do not have any double quotes within the double quotes of the entire line. Use single quotes within the double quotes.

# re: How can I extract XML from SQL using DTS? 1/7/2004 8:39 AM Kathy

Hans,
Make sure you do not have any double quotes within the double quotes of the entire line. Use single quotes within the double quotes. If that is not the problem, post your entire sSQL line for more help. :)

# re: How can I extract XML from SQL using DTS? 1/7/2004 12:13 PM Kathy

I found the answer to my own question. Thought I'd post the answer in case anyone else has the same problem. You can not use aggregate functions in your query when using the FOR XML AUTO.
I got this text excerpt from Ken Henderson's - The Guru's Guide to SQL Server Architecture and Internals - Chapter 18 - "NOTE: Currently, AUTO mode does not support GROUP BY or aggregate functions.
The heuristics it uses to determine element names are incompatible".
So in order to make this work, you would need to use the "EXPLICIT" mode. Hope that helps someone else as it was driving me crazy! :)
with these constructs, so you cannot use them in AUTO mode queries. Additionally,
FOR XML itself is incompatible with COMPUTE, so you can’t use it in
FOR XML queries of any kind."

# re: How can I extract XML from SQL using DTS? 1/7/2004 12:23 PM Kathy

For all of you with questions on this sample, you might want to check out this sample chapter of 'The Guru's Guide to SQL Server Architecture and Internals'. The sample chapter explains all of what you need to know to REALLY understand what is being done in this example. I HIGHLY recommend it. :)
http://www.sqlteam.com/books/henderson_Ch18.pdf

Also, just wanted to say to SQLXML.ORG - Thanks for posting this example and message board. It saved me a whole lot of time and really got me going in the right direction! KUDOS!!! THANKS!!! :)

# re: How can I extract XML from SQL using DTS? 1/13/2004 11:47 AM Kathy

But I was able to successfully use the aggregate function in a stored procedure, and then call the stored procedure like this:

sSQL = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>"
sSQL = sSQL + "EXEC storedProcedureNameHere</sql:query></ROOT>"

# re: How can I extract XML from SQL using DTS? 2/19/2004 10:19 AM Jessica

Anyone knows how to pass a parameter into this script?

# re: How can I extract XML from SQL using DTS? 2/20/2004 5:20 AM Hans Brouwer

Hi Kathy, sorry for the delay, was working on another project.
I may have found what was wrong. My script is exactly as at the start, exept for the sSQL line. I it seems I need to add some concatenations, then at least the script will compile correctly:
sSQL = "<ROOT xmlns:sql=" & urn:schemas-microsoft-com:xml-sql & "><sql:query>" & "select * from Categories for xml auto</sql:query></ROOT>"

But now... I receive this error when running the script:

... Errordescription: Type mismatch: 'schemas'
Error on line 13.

Line 13 is the sSQL line.
So, should I NOT add the concatenation after the ROOT string? But then my script won't compile. Or is there another problem here of which I am not aware?

Tnx,
Hans Brouwer

# re: How can I extract XML from SQL using DTS? 2/23/2004 12:04 PM Kathy

Maybe try this??

sSQL = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>"
sSQL = sSQL + "Select * from Categories for XML auto</sql:query></ROOT>"

# re: How can I extract XML from SQL using DTS? 2/24/2004 10:28 AM Kathy

Jessica,
If you are using an Active X Script task within DTS you can use a global variable in the DTS package and utilize it within the script like this:

dim TmpStr
TmpStr = DTSGlobalVariables("VariableName").Value

# re: How can I extract XML from SQL using DTS? 2/25/2004 1:49 PM Jessica

Thank Kethy. I didn't figure out if I could set DTSGlobalVariables("VariableName")'s value in a job or a trigger. So I gave up it.

# re: How can I extract XML from SQL using DTS? 3/2/2004 11:28 AM Cezar

I tried the script but when ti comes to execute this line
oCmd.Execute , , 1024

it's saying:
"MSXML3: A string literal was expected, but no opening quote character was found"

Can anyone help with this?

Thanks

# re: How can I extract XML from SQL using DTS? 3/4/2004 12:05 PM Kathy

Try installing MSXML4.0 SP2. Your error indicates you have MSXML3 installed. MSXML 4.0 is required for this code to work (note the comment in the code "If MSXML 4.0 is not installed this will not work!")
=D

# re: How can I extract XML from SQL using DTS? 3/17/2004 5:06 AM Wayne

My script runs but produces no output from the table. There are rows to select in the table bul all that is in the .xml file is this:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sq"><sql:query>select * from ca_callrecord for xml raw</sql:query></ROOT>

Here is the entire script. Thanks in advance for any help.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

Dim oCmd, sSQL, oDom

''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Msxml2.DOMDocument.4.0")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "--connection string--"

sSQL= "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sq""><sql:query>select * from ca_callrecord for xml raw</sql:query></ROOT>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "c:\temp\testdts.xml"

Main = DTSTaskExecResult_Success

End Function

# re: How can I extract XML from SQL using DTS? 3/17/2004 5:12 AM Wayne

Never mind I found it.

This:
urn:schemas-microsoft-com:xml-sq

Should have been this:
urn:schemas-microsoft-com:xml-sql

I deleted the "l" when fixing the quotes.

Thanks. Wayne

# re: How can I extract XML from SQL using DTS? 3/24/2004 8:56 AM Gill

We used to use BPC to port out query results sets into files from Sql Server 2000. However BCP has now been taken away from us.
I am now trying to port XML into a file from DTS, but everytime we do I just get binarybase64 instead.
Using a ODBC connection we do get the correct format, but half the data is missing from the end file?? Does anyone know anyway of getting round this issue?
It has been suggested we use the writefile object to port it out, but it requires 2 columns where the results set only has one.
Any ideas????

# re: How can I extract XML from SQL using DTS? 4/11/2004 6:56 AM Haiko Burkhardt

BCP still works, and we use it and works like a charm!...

# re: How can I extract XML from SQL using DTS? 4/12/2004 5:53 AM Haiko Burkhardt

BCP still works, and we use it and works like a charm!...

# re: It works even if you don't have MSXML 4.0 4/19/2004 2:45 PM Lipa Geodakova

'**********************************************************************
' Visual Basic ActiveX Script
' XML document thru MSXML2.dll
' Even if you don't have MSXML 4.0
' - it works!!
'************************************************************************

Function Main()
Dim xmlDoc ' as DOMDocument
Dim adoCmd ' as ADODB.Command

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
Set adoCmd = CreateObject("ADODB.Command")

adoCmd.ActiveConnection ="Provider=SQLOLEDB;Data Source = SRVR_NAME;Initial Catalog = DB_Name;User ID= user_name;Password=xyz"

adoCmd.Properties("Output Stream") = xmlDoc

adoCmd.CommandText = "StorProc_For_Explicit"
'*** you have to create StorProc with root element

adoCmd.Execute , , 1024

xmlDoc.Save "d:\Temp\testdts.xml"

set adoCmd=nothing
set xmlDoc=nothing

Main = DTSTaskExecResult_Success
End Function

# re: How can I extract XML from SQL using DTS? 4/23/2004 1:15 PM mthomas

I get this error , somebody help please , I really need this to work. I really appreciate your help. Thanks in advance.

Below is the statement I have in the script....

Line 9
Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB;Server=local;database=Northwind"

# re: How can I extract XML from SQL using DTS? 4/23/2004 1:16 PM mthomas

# re: How can I extract XML from SQL using DTS? 4/23/2004 2:03 PM mthomas

sorry , I did not mention the error -
" Invalid authorization specification "

# re: How can I extract XML from SQL using DTS? 5/12/2004 8:50 PM Bryant

You need to provide the user id and password...

# re: How can I extract XML from SQL using DTS? 5/25/2004 7:52 AM Mervyn

The resulting file that i have output to my harddrive as Xml is fine, but it is too Large how could i cut it up into smaller segments that would be more manageable.

Thanks

# re: How can I extract XML from SQL using DTS? 5/25/2004 9:37 AM Bryant

You would need to break up your query into smaller pieces then.

# re: How can I extract XML from SQL using DTS? 5/27/2004 9:22 PM Scott

I have all the newest "stuff" and this code still fails - Windows 2003, MSXML 4.0 SP2, yada-yada...

It's the oCmd.Execute , , 1024 line that nails me.

# re: How can I extract XML from SQL using DTS? 5/27/2004 9:32 PM Scott

or the oCmd.Properties("Output Stream") = oDom line; I don't know which...

# re: How can I extract XML from SQL using DTS? 5/27/2004 9:43 PM Scott

SOLVED IT:

I was getting this error:

"MSXML3: Switch from current encoding to specificed encoding not supported"

I'm not running MSXML3 (can't run xmlinst anymore so I'm using 4.0).

The issue was that I was trying to add in a processing instruction to use UTF-8...

Don 't add a pi to change the encoding...

Thanks for the script.

# DTS and FOR XML EXPLICIT 6/15/2004 3:13 PM Chris Bassett

This solution works great where you have simple selects, but I get a zero-byte file when calling a stored procedure that has the FOR XML EXPLICIT.

Should I just scale up to .NET toolsets and be done with VBScript??

# re: How can I extract XML from SQL using DTS? 7/19/2004 7:26 AM Ash

hey,
I dont know if this question is appropriate here, but since its related I thought I cud ask.
I am executing a stored procedure in an ActiveX script in a DTS package and I want to evaluate if the stored proceure executed correctly or errored out. Basically, check the RETURN_VALUE contents. I am using a Recordset.Open command to exec the stored proc...
Is there any way I can do that ?
Ashwin

# re: How can I extract XML from SQL using DTS? 7/19/2004 7:53 AM Ash

# re: How can I extract XML from SQL using DTS? 9/14/2004 1:22 PM Mallikvs

I have 221,000 records to be exported to XML file . does this work? when i run this code . CPU usage goes to 100% and my memory usage is constantly raising. I need some thing that reads data in chanks and writes in chunks . to keep resource usage at low . time it takes to upload really is not an issue. does any one know how to do this?

# re: How can I extract XML from SQL using DTS? 11/11/2004 7:41 PM Aranda

I am also having trouble with the line

oCmd.Execute , , 1024

When I have a simple Query it works fine but when I try anything with a few joins I get the error...
"MSXML3: Whitespace is not allowed at this location"

Anyone know what fixes this problem?

# re: How can I extract XML from SQL using DTS? 3/18/2005 11:18 AM Bill Bohlen

HERE IS A TIP:

If your SQL Query contains any characters that XMl doesn't like, such as greater than(>) or less than(<), you will get an MSXML error. If you try to encode these characters in the CommandText argument (%lt; or %gt;) you will get a SQL error. Two ways around this:

1) Format your complex query as a stored procedure and adjust the CommandText parameter (sSQL variable) above to call the SP.

OR

2) Format the CommandText like this:

sSQL = "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query><![CDATA[query_text_goes_here]]></sql:query></ROOT>"

# re: How can I extract XML from SQL using DTS? 3/18/2005 11:30 AM Bill Bohlen

# re: How can I extract XML from SQL using DTS? 5/17/2005 12:17 PM Sheetal

Thank you very much for posting this!!!

# re: How can I extract XML from SQL using DTS? 7/11/2005 11:29 AM em

How can I specify the encoding of the file output?

# re: How can I extract XML from SQL using DTS? 7/13/2005 9:16 PM Hrair Kerametlian

How could we tweak the code to output the xml schema with with the xml file produced? Some systems demand the schema be present with the data.

# re: How can I extract XML from SQL using DTS? 11/28/2005 11:34 PM Nitin

I am habing problems with the same line

oCmd.Execute , , 1024

I get this error i tried all the things in this posting but cant locate the error

---------------------------
Create Document
---------------------------
Error Code: 0

Error Source= Microsoft OLE DB Provider for SQL Server

Error Description: Timeout expired
Error on Line 44
Timeout expired
Timeout expired
Timeout expired
Timeout expired
Timeout expired
Timeout expired
Timeout expired

---------------------------
OK
---------------------------

# re: How can I extract XML from SQL using DTS? 11/28/2005 11:35 PM Nitin

when i execute the DTS STEP as Standlone Step it executes fine but when i execute it as a package it screws up

# re: How can I extract XML from SQL using DTS? 12/1/2005 9:33 AM Jayaram Krishnaswamy

The script shown works quite well. However, if comments were added about the 'dialect', 'stream' etc it would have been helpful. The main reason that DTS, as is, does not support appears to be the invalid characters that are put out as part of the query especially the cryptic string that appears as a column name in the Query Analyser if one runs the forXml query.
The package as well as the stand alone script works. I do not see any relation to DTS in the script except the last line. Does it mean that this runs independent of DTS?

# re: How can I extract XML from SQL using DTS? 12/1/2005 9:47 AM Jayaram Krishnaswamy

# re: How can I extract XML from SQL using DTS? 12/8/2005 10:52 AM Mike Jeppesen

The scrip runs great on my development machine, but when I moved to my production server I get the following error: "ActiveX Component can not create object: 'Msxml2.DOMDocument.4.0'"

Any Ideas?
Thanks

# re: How can I extract XML from SQL using DTS? 12/8/2005 10:52 AM Mike Jeppesen

# re: How can I extract XML from SQL using DTS? 4/20/2006 11:30 AM vasanth

Try this working fine

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim xmlDoc ' as DOMDocument
Dim adoCmd ' as ADODB.Command

Set xmlDoc = CreateObject("Msxml2.DOMDocument.4.0")
Set adoCmd = CreateObject("ADODB.Command")

adoCmd.ActiveConnection ="Provider=SQLOLEDB;Data Source = servername;Initial Catalog = dbname;User ID= sa;Password=pass"

adoCmd.CommandText = "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><audit><sql:query><![CDATA[select * from tablename for xml auto, elements]]></sql:query></audit></ROOT>"

adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

adoCmd.Properties("Output Stream") = xmlDoc

adoCmd.Execute , , 1024

xmlDoc.Save "D:\SampXml\testdts.xml"

set adoCmd=nothing
set xmlDoc=nothing

Main = DTSTaskExecResult_Success

End Function

# re: How can I extract XML from SQL using DTS? 4/24/2006 6:35 AM Taylor

If I run it through the Microsoft Console, this works. If I try to instantiate it from a SQL Script using sp_OAMethod I get an error message relating to Microsoft SQL Server Extensions for XML. These suggest that msxml3.dll should be used and not 4. If I remove 4 register 3, again it runs fine through MMC however I get a very generic ActiveX run time error for the CreateObject call of MSXML2.DomDocument.3.0. Any suggestions?

# re: How can I extract XML from SQL using DTS? 5/26/2006 8:10 AM bolsen

Been working great in sql2000. Now I am trying to upgrade to sql2005 and I get an "Function not found." error if I copy the code to an activeX script task.
The xml task seems to deal with xml in a table, is there an easier way to do this in 2005?

Thanks,

# re: How can I extract XML from SQL using DTS? 6/19/2006 9:03 AM bolsen

I wrote an assembly using c#, got it to work in 2005. Pain in the ascii though...

Nice link: http://www.sqldbatips.com/showarticle.asp?ID=23

# DTS Package outputs a 0-byte file 10/10/2006 8:32 AM New with DTS

I have a DTS package that i cannot figure out why it outputs a 0 byte file after it runs.

# DTS Package outputs a 0-byte file 10/10/2006 1:05 PM New with DTS

I have a DTS package that i cannot figure out why it outputs a 0 byte file after it runs.

# DTS Package outputs a 0-byte file 10/10/2006 1:05 PM New with DTS

I have a DTS package that i cannot figure out why it outputs a 0 byte file after it runs.

# DTS Package outputs a 0-byte file 10/10/2006 1:05 PM New with DTS

I have a DTS package that i cannot figure out why it outputs a 0 byte file after it runs.

# re: How can I extract XML from SQL using DTS? 10/26/2006 10:16 AM lily

Thanks for the script, it works! and got the xml file too. But i don't want the root element(<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">) in my xml file.

Is there anyway i can remove this line??

Thanks
Llily

# re: How can I extract XML from SQL using DTS? 10/26/2006 10:56 AM lily

# re: How can I extract XML from SQL using DTS? 6/7/2007 8:34 AM first time with MySQL

I need some helps about migrating MySQL to SQL Server...I have the xms database file in MySQL, how can import it to SQL Server Management Studio?
:)

# re: How can I extract XML from SQL using DTS? 6/7/2007 8:34 AM first time with MySQL

I need some helps about migrating MySQL to SQL Server...I have the xms database file in MySQL, how can import it to SQL Server Management Studio?
:)

# re: How can I extract XML from SQL using DTS? 7/14/2007 4:07 AM Meenakshisundaram

hai
what is defination of DTS

# re: How can I extract XML from SQL using DTS? 7/30/2007 1:42 PM Ali

thanks for the scripts

Function Main()
Dim oCmd, sSQL, oDom
''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Msxml2.DOMDocument.4.0")
Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;UID=sa;Password="

'sSQL= "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sq""><sql:query>select * from authors for xml raw,element</sql:query></ROOT>"

sSQL = "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><audit><sql:query><![CDATA[select * from authors for xml auto, elements]]></sql:query></audit></ROOT>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "d:\temp\testdts.xml"
Main = DTSTaskExecResult_Success
End Function

Working fine

# hlirsichio@emiz.com 8/13/2007 7:55 PM http://emiz.com

And Moscow girls make me sing and shou. Hilaire Shichiro.

# nuithale@uhhy.com 9/19/2007 12:23 PM http://uhhy.com

when they say it's ove. Nurit Hailey.

# re: Function Not Found Error 9/25/2007 3:45 AM Harry

Hello All,
I am using SQL Server 2005.I have downloaded MSXML 4.0. I keep getting the error "Function Not found" every time i try to execute the task. Cant figure out which line is causing the errors as the error logs don't point to any. If you have any tip please let me know.

Function Main()
Dim oCmd, sSQL, oDom
''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Msxml2.DOMDocument.4.0")
Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB;Data Source=(local);Initial Catalog=db1;UID=sa;Password=pwd1"

sSQL = "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><audit><sql:query><![CDATA[select Top 10 * from BaseData for xml auto, elements]]></sql:query></audit></ROOT>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "d:\temp\testdts.xml"
Main = DTSTaskExecResult_Success
End Function

# re: Function Not Found Error 9/25/2007 4:10 AM Harry

# Using a stored Procedure 9/25/2007 4:13 AM Harry

Hello All,
Sorry for the double post above.
Anyway, what would be the syntax for the sSQL string if one were to use a stored proc instead of the selet query?
Also, thanks to bolsen for the provided link.

# re: How can I extract XML from SQL using DTS 9/25/2007 4:47 AM Harry

Hey !!!
Got it working on SQL Server 2000 perfectly!!!
This script is amazing!

# re: How can I extract XML from SQL using DTS? 10/11/2007 9:26 AM xjmaoflami

Hello! Good Site! Thanks you! zaiwfwbfrjq

# gaumasa@xjxi.com 10/21/2007 1:51 PM http://xjxi.com

when they say it's ove. Gautam Asaf.

# re: How can I extract XML from SQL using DTS? 12/24/2007 1:44 AM Ritesh Gupta

Use BCP like
bcp " Select * from clientsimulator2..testgroup
for XML RAW" queryout c:\test1.xml -S PUNDL10996 -U sa -P sa -c -r -t

# re: How can I extract XML from SQL Store Procedure using DTS? 9/10/2008 11:44 PM ganesh

Function Main()

Dim ftp_address,ftp_username,ftp_password,strServer,strDatabase,strSQLuid,strSQLpwd

' assign the globe value to variable
strServer=DTSGLOBALVARIABLES("SQLserver").Value
strDatabase=DTSGLOBALVARIABLES("SQLDB").Value
strSQLuid=DTSGLOBALVARIABLES("SQLuid").Value
strSQLpwd=DTSGLOBALVARIABLES("SQLpwd").Value

' execute the Sp
'oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source="+ strServer +";Initial Catalog="+ strDatabase +" ;user id="+ strSQLuid +";password="+ strSQLpwd

Dim oCmd, sSQL, oDom
''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Msxml2.DOMDocument.4.0")
Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source="+ strServer +";Initial Catalog="+ strDatabase +" ;user id="+ strSQLuid +";password="+ strSQLpwd
sSQL ="<CSABC>"
sSQL = sSQL & "<sql:query xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
sSQL = sSQL & "exec ABC_WM_SpaarSaldo(put your SP Name) "
sSQL = sSQL & "</sql:query>"
sSQL = sSQL & "</CSABC>"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Encoding") = "utf-8"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "C:\Coupons.xml"
Main = DTSTaskExecResult_Success

End Function

# vroLQwDdaeFCTg 10/10/2009 5:41 PM Mark40

Pity the honest skeptic, should ever they meet you. ,

# vroLQwDdaeFCTg 10/10/2009 5:42 PM Mark40

Pity the honest skeptic, should ever they meet you. ,

# rrhAtaAMoFZ 10/12/2009 1:38 PM dqHjkNtn

doors.txt;10;15

# rrhAtaAMoFZ 10/12/2009 1:39 PM dqHjkNtn

doors.txt;10;15

# rrhAtaAMoFZ 10/12/2009 1:40 PM dqHjkNtn

doors.txt;10;15

Title
Name
Url
Comments
Remember Me?

FAQ #10

last updated:
11/6/2003

Did the information in this faq help answer your question?

Yes
No
Did Not Apply

Survey Results: 87

Yes

N/A


© 2001, 2002, 2003 sqlxml.org