XML to text file question...

[MarkButler]

I need to do what seemed like a very simple thing. Run a SQL query, producing XML output and saving that output to a text file which will be input to an EDI package.

After looking around the best I have come up with thus far is the "sp_makewebtask" method as follows:

sp_makewebtask @outputfile = 'c:\temp\authors.xml',
@query='select * from leaders for xml raw',
@templatefile = 'c:\temp\template.tpl'

The problem is that it likes to throw in extra CR and also does some translation of characters to their HTML equivelents. Has someone got an answer to this simple task? Many thanx in advance for any ideas.

Mark

 

[markVII]

Need a look at your template file...
The workaround would be using a DTS package to custom write your XML file.
There also might be possible to write a file using master..xp_cmdshell extended stored procedure...

 

[MarkButler]

The template file is nothing special. It supplies the high level XML name.

<?xml version="1.0"?>
<FROI>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</FROI>

I have today tried using the BCP command as in the following but it also has a max line length and inserts a CRLF after xx characters.

bcp 'select * from xxx for xml auto' queryout -c -Sxxx -Uxxx -Pxxx

How can something so simple be so hard?

Mark

 

[markVII]

XML in SQL2K was much of afterthought, not a full fledged citizen. It shows even in the approach itself - no SELECT INTO allowed, no special XML data type (which is present in Yukon)...As a consolation I could add that Oracle and DB2 did not much better.

I guess, you cannot fix you EDI package to look for these additional CR, right?

There are several choices I could suggest:
you could create your own version of sp_makewebtask. It makes a call behid the scenes to an extended stored procedure xp_makewebtask which accepts about 30 prameters; some of them might help you with the problem.

create your own extended stored procedure. Brush up your C/C++ skills

create a DTS task that produces equivalent output (my choice here)

play with isql/osql output redirection

 

[MarkButler]

MarkVII thanx for the response. You mention a DTS package. I have no experience with DTS other than interactive imports from various sources. I played with it a bit but did not see how to build a query and send its output to a XML text file. Can you point me to an example to get me started?

Such a simple task has been beating me up. any suggestions would be appreciated.

Mark

 

[markVII]

create a new package, add an ActiveX Script Task, paste in the script, run it....:
-------------------
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=pwd;Password="

sSQL = "<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>" & _
"select * from AUTHORS 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 "C:\temp\testdts.xml"

Main = DTSTaskExecResult_Success

End Function

 

[MarkButler]

Thanx for the reply. I looked around the net and found one very similar to what you had suggested and it worked great. Again thanx.

 

[CrystalVis]

MarkButler,

Can you please post what you find on the Internet. Thanks