SQLXML 4.0

Question

SQLXML 4.0

smart_research

SSC Eights!

Points: 805
More actions
October 30, 2009 at 4:12 am

#216942

SQLXML was a great feature of sql server 2000 to work with xml data. Is it still valuable and what are your reccommandations about working with xml?
SQLXML is still supported for Win Server 2008 and SQL Server 2008 but "only" through .net classes. Before there was the isapi component which allowed to access sqlxml templates easily through a simple url.
IIS-support: http://msdn.microsoft.com/en-us/library/aa275456%28SQL.80%29.aspx
4.0 way: http://msdn.microsoft.com/en-us/library/ms171785.aspx
Do you think sqlxml 4.0 it is still worth to use?
One issue Im facing:
While migrating the SPs in order that they return typed xml (for xml path, type) i realized that many of them return multiple result sets containing parts of the resulting xml
This means that one has to:
- iterate over all resultsets returned
- iterate inside every resultset over the rows
For the templates it doesnt seem to be a big issue from point of performance but implementing it with ADO/jdbc seems harming performance. Should one avoid to return multiple resultsets containing xml fragments and resulting in a big xml returned?
Thanks for your comments.
Have a nice day.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

In my opinion, XML support in SQL Server 2005/2008 is way better than 2000. In SQL Server 2000 you first had to create a document handle to work with. In addition it was restricted to 8000 characters. Since 2005 XML is a build-in feature of SQL Server and gives way more possibilities (with a maximal restriction of 2GB).

I did not yet have any performance problems with new XML functions. There shouldn't be too much in SQL, though.

If you got any performance problems, could you be a bit more specific about it?

Greets

Flo

smart_research SSC Eights! Points: 805 More actions · Answer 2

I totally agree that xml support in 2005/2008 is far better.

My question is more about how to access a SP that returns xml.

I wanted to know how you access complex xml data that is gathered from several tables, procedures.

Do you use SQLXML 4.0? Would you recommend it and why?

The ISAPI component of sqlxml is not anymore available. "removal of SQLXML ISAPI support, you cannot use IIS virtual directories with SQLXML 4.0."

In c# I can either access a SP an iterate over the ResultSet(s) it returns OR open a sqlxml template and execute it:

using Microsoft.Data.SqlXml;

....

SqlXmlCommand cmd = new SqlXmlCommand(ConnString);

XmlDocument Template = new XmlDocument();

Template.Load("c:\example\pathtotemplate\template.get");

cmd.CommandType = SqlXmlCommandType.Template;

cmd.CommandText = Template.DocumentElement.OuterXml;

{add Parameters }

Stream Result = cmd.ExecuteStream();

...

---- file:template.get -------

<?xml version="1.0" encoding="UTF-8" ?>

<sql:query>

exec MyDB.dbo.SP_Test

</sql:query>

</RootTag>

---- end file:template.get -------

My first impression is that if the SP returns multiple ResultSets (each of them containing one ore more rows) it is still faster to access the SP with a template, although i cannot make use of the xml datatype (well formed xml is not really guaranteed)

In General: Would you recommend using sqlxml templates?

Thanks for your suggestions.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi,

I don't use SQLXML, I use the .NET framework standard features from System.Data namespace.

Here two different samples:

using (SqlConnection cn = new SqlConnection("Server=.\\Sql2k8;Database=Sandbox;Integrated Security=sspi;"))

{

cn.Open();

XmlDocument dom;

SqlCommand cmd;

XmlReader xr;

// using ExecuteReader

cmd = new SqlCommand("SELECT CONVERT(XML, '<root><foo>bar</foo></root>')", cn);

xr = cmd.ExecuteXmlReader();

dom = new XmlDocument();

dom.Load(xr);

// using parameter (e.g. returned by procedures)

cmd = new SqlCommand("SELECT @p1 = CONVERT(XML, '<root><foo>bar</foo></root>')", cn);

SqlParameter p1 = cmd.Parameters.Add("@p1", SqlDbType.Xml, -1);

p1.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

// System.Data.SqlTypes.SqlXml

SqlXml xmlValue = p1.SqlValue as SqlXml;

xr = xmlValue.CreateReader();

dom = new XmlDocument();

dom.Load(xr);

}

Greets

Flo

PiMané SSChampion Points: 13965 More actions · Answer 4

Hi,

Be careful how you use XML in SQL Server.

It's fine for storing data that requires no search on it. I use it to store properties on data that isn't searchable and every time some asks me to add another field I just say: use the XmlParameters field. At the database level you don't have to change a thing: insert, update, delete, get, list...

But if you think of using XML do make some queries over it, say for example, have a XML field to store the address: <address><street></street><city></city>...</address>, even with XML indexes and other features introduced to XML in SQL 2005 and allegedly improved in 2008, the truth is that it doesn't perform very well and "kills" the database.

XML is great to store data not searchable...

Pedro

If you need to work better, try working less...

smart_research SSC Eights! Points: 805 More actions · Answer 5

Hello,

I would also not recommend to store data in xml format for wich you need performant searches, although I think when one adds a typed xml (with the schema) to a column an adds xml indexes the data can in fact be accessed and searched as fast as traditional relational data.

The SP I mentioned in the examle is working completly with relational data and just transforms the resultset to xml at the end of the query.

For example with:

select ID, Name

from table1

for xml path('Company'),type

This readily enables the creation of WebServices.

Anybody using SQLXML to access resulting XML from sql server?

Greetings!

PiMané SSChampion Points: 13965 More actions · Answer 6

The SQL documentation about XML suggests that when using INDEX over XML schema's can be as fast as a normal table.

Unfortunately that isn't so...

I made some test, with over 2.000.000 records and when using normal tables I had 500% gains in performance, or more, depending on the queries I made.

Pedro

If you need to work better, try working less...

jill.heyser SSC Enthusiast Points: 108 More actions · Answer 7

Did you ever find a solution to this? We also use SQLXML to publish XML from stored procedures on SQL Server.

Thanks in advance,

Jill

smart_research SSC Eights! Points: 805 More actions · Answer 8

Our new Web-Services were Java-based with jdbc the only way we found was to change all our SPs to return only one Result-Set (with one xml column). Otherwise there was a performance impact.

From .net we managed to use the SPs respectivly SQLXML templates wich returned multiple result-sets "internally" and in the code we got just one xml as result without notable performance impacts.

Hope that helps. Good Luck.