• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

SQL 2008 XML Export problem

Hi Experts.  I want to export a table from SQL 2008 to a xml file.  I understand that the export functionality does not exist as a wizard within the application.  I have created a solution by using bcp
"SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -t but the file created is not a good format.  Can anyone help in a solution to export a simple table to xml from SQL 2008 which formats the file.
The table consists of 5 columns test,test1,test2,test3,test4 with 3 rows of data.

The data output using the bcp above is shown below :
<row Test="3         " 
test2="2008-10-10T00:00:00"
test3="1223243"
test4="erf454    "
test5="213243242"/>
<row Test="4         " 
test2="2008-10-12T00:00:00"
test3="1223243"
test4="erf454    "
test5="213243242"/>
<row Test="5         " 
test2="2008-11-10T00:00:00"
test3="1223243"
test4="erf454    "
test5="213243242"/>

Any help would be greatly appreciated!

Thanks in advance
0
chrislindsay
Asked:
chrislindsay
  • 5
  • 4
1 Solution
 
lofCommented:
What would you like to change in the output? How would you like it to be formated, stored?
0
 
lofCommented:
maybe that's something you are after?
select * 
from sampletable 
for xml 
path ('DataRowCustomName'), 
root('RootTag')

Open in new window

0
 
chrislindsayAuthor Commented:
Hi Lof,
Thanks for replying.
That works great in a query.  
I need to export the query above to a file.
Any Ideas?

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
lofCommented:
There are few options.

If you want to do it from outside SQL server you have bcp, osql and sqlcmd tools you can use.
If you want it to be part of a stored procedure for example, you may enable xp_cmdshell like that:

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure

and then do something like:

declare @xml as xml
set @xml = (
select *
from hcte
for xml
path ('DataRowCustomName'),
root('RootTag')
)

declare @cmd as varchar(4096)
set @cmd = 'echo "' + convert(varchar(max),@xml) + '" > C:\data.xml'
exec master..xp_cmdshell @cmd

You can create UDF in .Net language that will create file for you

or alternatively you can instantiate Windows Scripting Object and through that get access to a file system. I had examples for few of the methods as I was trying different approaches in past years.
If you are interested in any particular method, let me know and I will try to upload a sample
0
 
chrislindsayAuthor Commented:
hi lof,

This is great. Thanks.
The script creates the file in the C: root
The only problem with the XML file (data.xml) is the " marks at the start and the end of the file.
"<Data>
<Row>
<Test>3         </Test>
<test2>2008-10-10T00:00:00</test2>
<test3>1223243</test3>
<test4>erf454    </test4>
<test5>213243242</test5>
</Row>
</Data>"


Is there a way of taking the " marks out of the start and end of the file by modifying the script?

Thanks
0
 
lofCommented:
With echo as far as I am aware, it is not possible as < and > characters are of special meaning for the command shell.

You may write a small vbs script like that

Dim ArgObj
Set ArgObj = WScript.Arguments
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set ObjFile = ObjFSO.CreateTextFile(ArgObj(0))
ObjFile.WriteLine(ArgObj(1))
ObjFile.Close

and save it in tofile.vbs somewhere on your drive

then change last fragment of SQL to

declare @cmd as varchar(4096)
set @cmd = 'C:\path\to\tofile.vbs C:\data.xml "' + convert(varchar(max),@xml) + '" '
exec master..xp_cmdshell @cmd

0
 
chrislindsayAuthor Commented:
Thank you Lof. The Solution words perfectly
0
 
chrislindsayAuthor Commented:
Hi Lof,

I have tried using larger datasets using your solution above.  The query just hangs.
I assume it's something to do with the varchar(4096).
Is there a work around as the xml files I am trring to create are appox 100 rows with 6 fields.

Thanks
0
 
lofCommented:
it all depends on your data. firstly you may try varchar(max) rather than varchar(4096)
if it is still not enough and the problem is in the varchar limitations you may change the script slightly so you can execute tofile.vbs few times with the same file to append data and then to it in parts.

It's sometimes tricky to get it right with those windows scripting object but I was successfully creating big and very complex files with this method
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now