March 19, 2009 at 5:09 am
I run the following query on a view and get an XML result.
select * from TESTVIEW as Account for xml auto
When I select the results and view the XML results it is in the format I want. If I then save the xml file it saves in in the correct format. My question is how can I automate this process. I need this file every day and do not want to have to manually do it every night. I have tried BCP but have been unable to get that to work. Any help would be greatly appreciated.
March 19, 2009 at 5:24 am
Can you show us what you tried and what error you got? I’ve done if few times with no problems at all.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2009 at 7:40 am
I ran the following query for SQL Server Management Studio "select top 10 * from TESTING as Account for xml auto"
I then clicked on the result and saved it as TEST.xml Below is a copy of the text version of that file.
This is the format I need. It is perfect! I tried running BCP then to be able to automate this process. Here is the BCP command I'm using. "C:\>bcp "SELECT top 10 * from AdventureWorks.dbo.TESTING as Account FOR XML AUTO" queryout c:\test2.xml -Sclark05 -T -w -r -t"
Below is a copy of the text version of that file. It puts everything on one line.
<Account
I then have a header and footer file I need to apply to this file. The concatenation of all three is what I need. It works with the first file from the Management Studio but not with the second one using BCP. When I concatenate them I get a "ÿþ" character between the header and the main document and I get extra spaces between all the letters. Example below. Notice the character addition and all the extra spaces.
ÿþ< A c c o u n t C u s t o m e r I D = " 1 " T e r r i t o r y I D = " 1 " A c c o u n t N u m b e r = " A W 0 0 0 0 0 0 0 1 " C u s t o m e r T y p e = " S " /
I do not have either of these problems when using the file created from SQL Server Management. Hopefully, this explains it better. There may be a better way of doing it that I'm not aware of. I'm open to any and all suggestions. Automating the extract from Management Studio would be ideal.
March 19, 2009 at 9:07 am
Figured it out.
Needed to configure the output from bcp using the -t and -r parameters. Thanks.
SELECT top 10 * from AdventureWorks.dbo.TESTING as Account FOR XML AUTO"queryout c:\test.xml -Sclark05 -T -c -t ";" -r ""
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply