Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 FOR XML AUTO Formatting

Author  Topic 

chrisgin
Starting Member

8 Posts
Posted - 2004-11-13 : 19:26:53
I'm trying to find the easiest way to output an XML file from a SQL Server 2000 query. The FOR XML AUTO clause seems to be sufficient for our purposes apart from the formatting - the XML needs to be nicely indented etc so that it's reasonably easy for a person to read.

Is there a way to achieve this formatting using FOR XML AUTO? If not, what is the easiest alternative method? Someone suggested using a VBS script with XML DOM. I'm not too familiar with this so before I start doing some research into it, I just want to check I'm on the right track.

Any help appreciated,
Chris

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-13 : 19:36:17
Nope. XML AUTO won't indent, neither will XML EXPLICIT. Indenting in an XML file is purely cosmetic, it doesn't alter the content of the XML. You can use XML DOM or an XSL stylesheet to format the XML generated by SQL Server. This site has tutorials on XML, DOM, and XSL:

http://www.w3schools.com/default.asp

Although, having "nicely formatted" XML isn't really worth the effort. If a person actually needs to read it, XML is hardly readable compared to plain old text.
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-13 : 19:47:50
Thanks for the quick reply. I'll look into the link regarding XML DOM and XSL.

Is it an alternative for the client to convert a normal non-XML result set into XML rather than getting SQL Server to do it? Would that then give more options with regards to formatting?

Chris
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-13 : 19:55:49
One thing I forgot to mention, the files we are looking at creating would be quite big, in the hundreds of megs range. Does the FOR XML clause in SQL Server handle large result sets?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-13 : 21:28:33
It should be able too, but I've never tried it. I doubt that would be the most effective way to deliver it though. Files that size might benefit from having the client process the data into XML. Doing it on the server, while probably faster, will take longer to transmit over the network because the XML elements will significantly bloat the results.

Why would a client be receiving hundreds of megs of data? Sending this amount of data to a client is almost always a bad sign. Can you describe exactly what is being sent and why?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-14 : 03:24:26
Internet Explorer will format ["pretty print"] a file containing XML - is that's any help. And then there are loads of utilities available. Don't forget that formatting whitespace will probably be greater than the actual content, so will double the filesize / transmission time.

Kristen
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-15 : 17:27:53
>> Why would a client be receiving hundreds of megs of data? Sending this amount of data to a client is almost always a bad sign. Can you describe exactly what is being sent and why? <<

We are looking at converting our existing file-based extracts to XML. These are things like Statement extracts that go to a print-house. Currently they are all in a proprietary format so we're looking at using XML instead to make it easier for third parties to process.

Chris
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-15 : 18:01:47
I tried to do a simple test by creating an XML file using FOR XML AUTO and then viewing it in Internet Explorer to see how it formats it, however IE is giving an error when trying to display the file.

I used the following SQL to generate the XML:

SELECT * FROM pubs..authors FOR XML auto, elements

I saved the results to a .XML file and then opened in IE. It gives the following error:

A name contained an invalid character. Error processing resource 'file:///C:/Temp/test2.xml'. Line 4, Position 4

ame>MacFeather</a
---^

Looking at that particular line in the file, the only strange thing I can see is that the element is wrapped across two lines, i.e.:

<au_ln
ame>MacFeather</au_lname>

Is this not valid XML? And if not, how do you get around this?

Thanks,
Chris
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-15 : 19:23:02
Line breaks within a tag are not well-formed XML, so it cannot be parsed.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-16 : 00:18:11
Query Analyser will produce line breaks at whatever column width you have configured it for, plus it will output a column heading and line of "------"

You'll need to create the file with a different tool, or hand-edit it to remove the line breaks

EDIT: Or use BCP

BCP "SELECT * FROM pubs..authors FOR XML auto,elements" queryout C:\TEST.XML -r \n -c -T -S Servername

You will need to form XML that includes a <ROOT> tag too

Kristen
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-16 : 15:44:39
I tried the BCP method suggested, and this does work for the data in the PUBS database. However, when I tried it on a table in one of our other databases, it still had line breaks in the middle of tags. The output file produced by BCP seems to be 2033 characters wide.

Isn't this always going to be problem regardless of the client tool used to execute the query? Unless the FOR XML clause is smart enough to not break up a tag across 2 rows, it will never be guaranteed to generate well-formed XML. Or is there a way to get around this? Perhaps a separate utility that can be run afterwards that will reformat the XML to be well-formed?

Chris
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-17 : 03:42:10
FOR XML isn't breaking the row, the client app. is.

Kristen
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2004-11-17 : 16:20:54
So there is no way to make a full size XML file from Query Analyser?

Is there a switch or something? I guess it would need to go
Options>Results to XML.

Anyways I was trying to do a bunch via sp_makewebtask and it was a no go. sp_makewebtask did handle the html creation nicely though. Close enough. THIS time....



I wish someone would start an Official XML Rant Thread.
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-11-17 : 18:50:40
quote:
Originally posted by Kristen

FOR XML isn't breaking the row, the client app. is.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q275583

This issue, unfortunatley, has no current work-around:

<patiently waiting for yukon/>

Chris, you might have a look at (Accessing SQL Server Using HTTP) in BOL.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-17 : 19:08:01
As that article mentions, the ADO Stream object is designed to handle streams of data, including XML. You can open the Stream, read the results into a variable, then write them into a file. Probably won't require more than 10-15 lines of code (VB, VBScript, C/C++/C#).
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-18 : 00:47:16
Yup, we do it with streams. I can't say it was altogether easy though ... but maybe thats 'coz we don't use streams much. We crashed the server several times before getting a working solution ...

Kristen
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2004-11-18 : 10:10:12
Thanks robvolk, Streams it is then.


but, crashed server!!!!! woooooooo doooooooooogieeeeeeeeee.




I wish someone would start an Official XML Rant Thread.
Go to Top of Page

chrisgin
Starting Member

8 Posts
Posted - 2004-11-18 : 18:10:54
Thanks everyone for your help. I guess I'll give ADO Streams a go.

Kristen, you mentioned it took a while to get it going - you wouldn't have a snippet of code that would get me started would you? I'm sure it's not that difficult, but having never really used ADO before it'll probably save me stuffing around that's all..

Chris
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-18 : 19:27:27
This is to send the XML, from an SProc, to a remote service - so might give you some ideas

	If strVarName = "" Then strVarName = "CLRCMRC_XML" ' Set Default

	Set objCmd = CreateObject("ADODB.Command")
	objCmd.ActiveConnection = dbconn

	'Open ADO Stream and set it as output for our ADO Command object
	Set adoStream = Server.CreateObject("ADODB.Stream")
	adoStream.Open
	objCmd.Properties("Output Stream") = adoStream

	'Build SQL Statement and add to our command object
	strSQL = "EXEC " & strSProcName & " " & strSQLParameters

	objCmd.CommandText = strSQL
	objCmd.CommandType = &H0001	' adCmdText
	
	'Execute our SQL and pass results into ADO Stream (as specified above: Output Stream = adoStream)
	objCmd.Execute , , 1024

	'Place adoStream contents into variable
	strPostData = adoStream.ReadText
	adoStream.Close

	Set objXML = Server.CreateObject("Microsoft.XMLHTTP")
	objXML.Open "POST", strURL, False
	objXML.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

	'Send contents of variable to Remote Service
	objXML.Send strPostData

	'Capture response from Remote Service
	strRetVal = objXML.ResponseText	' Returned value

... Process strRetVal ...

	Set objXML = Nothing
	Set objCmd = Nothing
	Set adoStream = Nothing

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site