SqlXml.org Main Feed

HOW TO: Get the XML text of a FOR XML query within SQL.

Bryant Likes — Sat, 11 Sep 2004 18:52:32 GMT

Is there a way to get the XML text of a FOR XML query?
Answer provided by Erland Sommarskog.
Check Erland's website for more great SQL Server information.

In SQL2000 you can do this:

   
create table xml (x ntext)
go
   
insert xml
SELECT * FROM OPENQUERY(MSDALOCAL, 
       'SELECT * FROM sysobjects FOR XML AUTO')
go
   
select * from xml
go

drop table xml

MSDALOCAL is a linked server, which can be a loopback to your own server. But, this is important, it has to be set up with MSDASQL - OLE DB over ODBC. If you use SQLEOLDDB, which is the default, then you get binary data instead.

If you want the data in a variable, beware that you cannot declare ntext variables in T-SQL, you would have to use nvarchar(4000) which may cause truncation.

In SQL2005 there is a xml datatype, all these sort of things are simpler.

Using Metaproperties in OpenXML

Bryant Likes — Wed, 12 Nov 2003 18:35:17 GMT

Goal: Using the following XML, insert three node records in the node table and then three two subnode records into the subnode table with a reference to the correct parent node. The node table uses a GUID as the node key.

 
<nodes>
  <node attr="123">
    <subnode val="789" />
    <subnode val="456" />
  </node>
  <node attr="123" />
  <node attr="321" />
</nodes>

If the attr values were unique then we could do a simple join on two OpenXML tables. However, since the attr values are not unique this cannot be done. The following SQL code demonstrates how this can be done. The code will be explained below.

 
declare @i int
 
-- prepare the document
exec sp_xml_preparedocument @i output, '
<nodes>
  <node attr="123">
    <subnode val="789" />
    <subnode val="456" />
  </node>
  <node attr="123" />
  <node attr="321" />
</nodes>'
 
-- create a table variable to store values
declare @t table(
 nodeid uniqueidentifier,
 oxid int, 
 attr int)
 
-- fill the table variable
insert into @t
select  newid(), id, attr
from  OpenXml(@i, 'nodes/node', 9) with (attr int, id int '@mp:id')
 
-- insert into our node table
-- insert into nodetable select nodeid, attr from @t
 
-- insert into our subnode table
-- insert into subnodetable
select  t.nodeid, ox.val
from  @t t,
 OpenXml(@i, 'nodes/node/subnode', 9) with (attr int '../@attr', val int, parentid int '@mp:parentid') ox
where t.oxid = ox.parentid
 
-- remove the xml document
exec sp_xml_removedocument @i

The code above makes use of the xml metaproperties that are available when using OpenXML. These properties are documented here.

In the first select statement we get the node id value using the @mp:id which gives us the unique id for each node element. Then in the next query we can get the @mp:parentid of the subnode which points to the @mp:id of the node. This allows us to figure out which node the subnode belongs to without needing a unique value in the node itself.

How To: Shape FOR XML AUTO results using SQL views

Bryant Likes — Tue, 04 Nov 2003 19:32:45 GMT

Sometimes it is easier to use FOR XML AUTO to retrieve XML from SQL Server than it is to use FOR XML EXPLICIT. However, if you're combining data from multiple tables you may not get results you expect using the AUTO mode.

Suppose that we want to get a list of the products in our database, but we also want to include the supplier name and category name. Below is an example template that you can run against the Northwind database.

 
<products>
 <sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
select  ProductID,
 ProductName,
 CompanyName,
 CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
 and Products.CategoryID = Categories.CategoryID
for xml auto
 </sql:query>
</products>

If you run the above template (or just run the select statement in query analyzer) you will get the right results, but the format leaves something to be desired. Below is a snippet of what the output will look like.

 
<products>
 <Products ProductID="1" ProductName="Modified product">
  <Suppliers CompanyName="Exotic Liquids">
   <Categories CategoryName="Beverages"/>
  </Suppliers>
 </Products>
 <Products ProductID="2" ProductName="Chang">
  <Suppliers CompanyName="Exotic Liquids">
   <Categories CategoryName="Beverages"/>
  </Suppliers>
 </Products>
 ...
</products>

All the data is there, but it isn't very readable and we are creating a lot of data bloat by adding the extra tags. We could use a FOR XML EXPLICIT query instead of the FOR XML AUTO or we could just use a SQL view. Below is the SQL statement to create our view. We just use the select statement with the FOR XML AUTO and create a view from it.

 
create view vw_ProductInfo
as
 
select  ProductID,
 ProductName,
 CompanyName,
 CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
 and Products.CategoryID = Categories.CategoryID

Once we have created our view we can modify our template. Now we will just select * from the view and add the FOR XML AUTO statement to it.

 
<products>
 <sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
select  *
from vw_ProductInfo as product
for xml auto </sql:query>
</products>

The "as product" will shape the resulting XML further since the elements will be named product instead of vw_ProductInfo. The resulting XML will be as follows.

 
<products>
 <product ProductID="1" ProductName="Modified product" CompanyName="Exotic Liquids"  CategoryName="Beverages"/>
 <product ProductID="2" ProductName="Chang" CompanyName="Exotic Liquids" CategoryName="Beverages"/>
 ...
</products>

So by using a SQL view the results of a FOR XML AUTO query can be shaped to the way we want it to look.

How To: Use OpenXML on an Xml document with a default namespace

Bryant Likes — Fri, 31 Oct 2003 14:53:36 GMT

Answer provided by Brian.

Hopefully this will prevent another developer from working until 12:30 AM to solve a mystery. I looked for the answer to this one for 4 hours. None of the documentation, newsgroups or experts out there give a simple example of how to use sp_xml_preparedocument with an xml document that had a single default namespace. They cover namespaces where the tags are prefixed and where multiple namespaces exist. But they don't cover the simplest case.

If you want lots of fun take the prefix off of the field names (change [a:name] to [name])and watch the query return NULL for the fields.

 
/****** run the code below in sql query analizer ********/
 
DECLARE @hDoc int
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
   '<request xmlns="http://www.mytest.com">
     <table>
      <name>NameField</name>
      <date>2003-06-14</date>
      <time>10:15</time>
     </table>
    </request>', 
  '<root xmlns:a="http://www.mytest.com"/>'
 
SELECT *
FROM OPENXML(@hDoc, '//a:table', 2)
WITH ([a:name] varchar(50), [a:date] varchar(50), [a:time] varchar(50))
 
EXEC sp_xml_removedocument @hDoc

The above code works by supplying the default namespace to the third parameter of the sp_xml_preparedocument stored procedure. Even though there is no prefix used for the default namespace, we still need to provide a prefix in order to reference the namespace.

How To: Insert and Update with OpenXML

Bryant Likes — Tue, 28 Oct 2003 22:02:16 GMT

I would like to update existing record if the ID exists in the table, otherwise insert the record(node) into the table..

This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.

 
declare @i int
 
exec sp_xml_preparedocument @i output, 
'<mydata>
  <test xmlID="3" xmlData="blah blah blah"/>
  <test xmlID="1" xmlData="blah"/>
</mydata>'
 
insert into test 
select xmlID, xmlData 
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)
 
update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
 
exec sp_xml_removedocument @i

So you can use the same openxml pointer to do an update and an insert.

How To: Combine FOR XML AUTO queries using XSL

Bryant Likes — Thu, 23 Oct 2003 01:51:03 GMT

When constructing complex XML documents, many times the best solution is not to use FOR XML EXPLICIT but rather use a bunch of FOR XML AUTO queries and combine them with XSL. Below are a few examples of how to accomplish this.

For these examples we will use the Northwind sample database.

Suppose that Northwind needed to send a supplier the contact information for each customer that had bought their products (maybe due to a recall). Further, the supplier needed to have that XML in the format shown below:

<Supplier SupplierID="1" Phone="(171) 555-2222">
 <Contact Name="Charlotte Cooper" Title="Purchasing Manager"/>
 <Customers>
  <Customer CompanyName="Alfreds Futterkiste" Phone="030-0074321">
   <Contact Name="Maria Anders" Title="Sales Represetative"/>
   <Products>
    <Product ProductID="1" ProductName="Modified product"/>
   </Products>
  </Customer>
 </Customers>
</Supplier>

Now you could create a FOR XML EXPLICIT query to do this, but it would be hard to read and hard to maintain because of the complexity of the XML document. So an alternative solution is to use simple FOR XML AUTO queries and then piece the information together using XSL. Below are the three queries we need to get all the data for our XML document.

select SupplierID, Phone, ContactName, ContactTitle 
from Suppliers 
where SupplierID = 1
FOR XML AUTO
 
select ProductID, ProductName 
from Products 
where supplierID = 1
FOR XML AUTO
 
select  distinct c.CompanyName, Phone, ContactName, ContactTitle, p.ProductID 
from  Customers c, Orders o, [Order Details] od, Products p
where  c.CustomerID = o.CustomerID
 and o.OrderID = od.OrderID
 and p.ProductID = od.ProductID
 and p.SupplierID = 1
FOR XML AUTO

So how do we get all these selects as one resultset? The first option is to use XML Templates. This option works fairly well and can be used with either the SqlXml Managed classes or the IIS ISAPI. Another option would be to use .Net and execute three queries and then paste them together.

First we'll look at a simple XML template that can be used to get these results and return them as a single document. Below is our template:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:header>
   <sql:param name="SupplierID"/>
 </sql:header>
 <sql:query>
  select SupplierID, Phone, ContactName, ContactTitle 
  from Suppliers 
  where SupplierID = @SupplierID
  FOR XML AUTO
 </sql:query>
 <Products>
  <sql:query>
   select ProductID, ProductName 
   from Products 
   where supplierID = @SupplierID
   FOR XML AUTO
  </sql:query>
 </Products>
 <Customers>
  <sql:query>
   select distinct c.CompanyName, Phone, ContactName, ContactTitle, p.ProductID 
   from Customers c, Orders o, [Order Details] od, Products p
   where c.CustomerID = o.CustomerID
    and o.OrderID = od.OrderID
    and p.ProductID = od.ProductID
    and p.SupplierID = @SupplierID
   FOR XML AUTO
  </sql:query>
 </Customers>
</root>

If we put this file in the virtual directory and create a mapping to it called supplier.xml we will get the following results when we execute the template (your results may vary slightly due to the data). I've also removed most of the customers to save bandwidth.

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 
 <Suppliers SupplierID="1" Phone="(171) 555-2222" ContactName="Charlotte Cooper" ContactTitle="Purchasing Manager"/>
 <Products>
  <Products ProductID="1" ProductName="Modified product"/>
  <Products ProductID="2" ProductName="Chang"/>
  <Products ProductID="3" ProductName="Aniseed Syrup"/>
 </Products>
 <Customers>
  <c CompanyName="Alfreds Futterkiste" Phone="030-0074321" ContactName="Maria Anders" ContactTitle="Sales Representative">
    <p ProductID="3"/>
  </c><c CompanyName="Antonio Moreno Taquería" Phone="(5) 555-3932" ContactName="Antonio Moreno" ContactTitle="Owner">
    <p ProductID="2"/>
  </c>
  ...
 </Customers>
</root>

Now that we have our XML results we can write the XSL to combine these and create our XML document. Below is an example of how you can do this with XSL. The XSL below will create the structure required.

<?xml version="1.0" encoding="windows-1252"?>
<xsl:stylesheet 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
        version="1.0">
<xsl:template match="/">
<Supplier>
  <xsl:attribute name="SupplierID">
    <xsl:value-of select="root/Suppliers/@SupplierID"/>
  </xsl:attribute>
  <xsl:attribute name="Phone">
    <xsl:value-of select="root/Suppliers/@Phone"/>
  </xsl:attribute>
  <xsl:apply-templates select="root/Suppliers"/>
  <Customers>
   <xsl:for-each select="root/Customers/c">
    <Customer>
     <xsl:attribute name="CompanyName">
      <xsl:value-of select="@CompanyName"/>
     </xsl:attribute>
     <xsl:attribute name="Phone">
      <xsl:value-of select="@Phone"/>
     </xsl:attribute>
     <xsl:apply-templates select="."/>
     <Products>
     <xsl:for-each select="p">
       <Product>
        <xsl:attribute name="ProductID">
         <xsl:value-of select="@ProductID"/>
        </xsl:attribute>
        <xsl:attribute name="ProductName">
         <xsl:value-of 
select="../../../Products/Products[@ProductID=@ProductID]/@ProductName"/>
        </xsl:attribute>
       </Product>
     </xsl:for-each>
     </Products>
    </Customer>
   </xsl:for-each>
  </Customers>
</Supplier>
</xsl:template>
<xsl:template match="Suppliers | c">
  <Contact>
    <xsl:attribute name="Name">
     <xsl:value-of select="@ContactName"/>
    </xsl:attribute>
    <xsl:attribute name="Title">
     <xsl:value-of select="@ContactTitle"/>
    </xsl:attribute>
  </Contact>
</xsl:template>
  
</xsl:stylesheet>

Once we have the XSL done we can save it and then set the sql:xsl attribute in our template to point to it. The result will now be transformed and will be in the format we want.

A few things to take note of:

When using the IIS virtual directory to run this template, your XSL file must be accessible. To check this type the URL to your XSL file and it should come up. I generally create one template mapping to a folder and put all my templates and XSL files in that folder.
When using the SqlXml Managed Classes you will need to set the BasePath property of the SqlCommand object to point to the folder where your XSL file is.

How To: Using XML BulkLoading with .Net Streams

Bryant Likes — Mon, 20 Oct 2003 23:59:31 GMT

Answer provided by Craig Pearson

Here is an example of how to use XML BulkLoading with .Net Streams.

using System.Runtime.InteropServices;
using SQLXMLBULKLOADLib;

[DllImport("OLE32.DLL", EntryPoint="CreateStreamOnHGlobal")]
extern public static int CreateStreamOnHGlobal( int hGlobalMemHandle, bool
fDeleteOnRelease, out UCOMIStream pOutStm);

public static void ExecuteBulkLoad(string connString, string schemaFile, 
                                          string userName, System.IO.Stream stream) 
{

    int i = System.Convert.ToInt32(stream.Length);
    byte[] dataBytes = new byte[i];
    stream.Position = 0;

    // declare the COM stream
    UCOMIStream data;

    // Create the stream (with no initial memory allocated)
    CreateStreamOnHGlobal(0, true, out data);
    int count = stream.Read(dataBytes, 0, i);
    data.Write(dataBytes, count, System.IntPtr.Zero);
    data.SetSize(i);

    // Create a bulkload instance as an single threaded apartment.
    System.Threading.Thread.CurrentThread.ApartmentState = System.Threading.ApartmentState.STA;
    SQLXMLBulkLoad3Class bulkLoad = new SQLXMLBulkLoad3Class();

    try {
        bulkLoad.ConnectionString = connString;
        bulkLoad.BulkLoad = true;
        bulkLoad.KeepIdentity = false;
        bulkLoad.XMLFragment = true;
        bulkLoad.Execute(schemaFile, data);
    }
    catch {
        throw;
    }
    finally {
        // close of the writer and the stream objects.
        if(stream !=null)
            stream.Close();

        bulkLoad = null;
    }
}

How do you setup SqlXml 3.0 on Windows Server 2003?

Bryant Likes — Fri, 13 Jun 2003 07:26:30 GMT

This question comes up quite often in the newsgroups. Here are the steps to getting this setup.

Download SqlXml 3.0 (this test was done with Sp2 Beta 1).
Run the installation program. You will need to also install the SOAP toolkit if you want to use the SqlXml SOAP features.
Open the IIS Virtual Directory Management tool under Start -> Programs -> SqlXml 3.0 -> Configure IIS.
Create a new virtual directory. Enter the information on the General tab and the Security tab. When you go to the Data Source tab you should be able to browse the databases on the server. If you can't then you need to check your security settings.
Turn on Allow sql= queries so that you can test your setup. Make sure you turn this off when you are done testing.

If you're using SqlXml 3.0 Sp2 Beta 1 then your server should be working at this point. The easiest way to test this is to open the following URL (assuming the server is localhost and the virtual directory is Northwind).
http://localhost/Northwind?sql=select%20@@version

If you're not using sp2, then you will have to enable the SqlXml ISAPI Extension. To enable this follow these steps:

Open the IIS Management tool (not the SqlXml one).
Click on the Web Service Extension folder under your server.
If you don't see SqlXml 3.0 listed, click Add a new Web service extension.
Put SqlXml 3.0 as the description and then add the SqlXml ISAPI which is found at: c:\Program Files\Common Files\System\Ole DB\sqlis3.dll (assuming default settings).

Run the test again and it should now work.

Why do I get a compilation error when importing the Microsoft.Data.SqlXml namespace?

Bryant Likes — Fri, 14 Mar 2003 19:21:49 GMT

This is a pretty common question that many ASP.Net developers run into. If you want to use the SqlXml managed classes in ASP.Net without using the code-behind feature, you will probably run into this problem. For our example we will use a very simply ASP.Net page.


<%@ Page Language="C#" Trace="false"%>
<%@ Import namespace="Microsoft.Data.SqlXml" %>

<script runat="server">
public void Page_Load()
{
	SqlXmlCommand command = new SqlXmlCommand("bogus");
}

</script>

If you create this ASP.Net page, add it to your site, and then access it, you should get a complilation error:

CS0234: The type or namespace name 'Data' does not exist in the class or 
namespace 'Microsoft' (are you missing an assembly reference?)

The simpliest fix to this problem is to just copy the Microsoft.Data.SqlXml.dll file from the Program Files\SQLXML 3.0\bin\ folder to the bin folder of your project. Once you do this the project should be able to find the reference. You should also be able to add a reference when you compile it.

How can I use aggregates and the group by clause with FOR XML?

Bryant Likes — Fri, 14 Mar 2003 16:57:06 GMT

Is there anyway I could return an XML with a SUM/GROUP BY clause in the select?!?

This is actually fairly simple to implement. For this example I will use the pubs database.

The first step is to create the query you want to use. For our example we will count how many titles each author has.


select 	count(*) titles, au_id
from 	titleauthor
group by au_id

If we were to add a FOR XML AUTO statement to the end of this code we would get an error. So instead we turn this statement into a view.


create view TitleCount
as
select 	count(*) titles, au_id
from 	titleauthor
group by au_id

Now we can use this view and create XML from it as follows.


select * from titlecount
for xml auto

Why do get an error "Incorrect syntax near the keyword 'WITH'"?

Bryant Likes — Fri, 15 Nov 2002 22:11:12 GMT

Thanks to Kelley Smoot for suggesting this FAQ!

This problem can occur for two reasons:

Your server is not running Microsoft SQL Server 2000.
Your server is running 2000, but is running in compatability mode (6.5 or 7.0).

If you're not running SQL 2000 then you can't use the XML features. To solve the compatability problem you have to switch to running in SQL Server 2000 mode. Check with your database administrator before changing this since there may be good reasons for it.

How To: Passing NTLM credentials when using ServerXMLHTTP.

Bryant Likes — Thu, 22 Aug 2002 18:28:30 GMT

Answer provided by Steven Sulzer

Suppose you do not have a proxy server, and want NTLM credentials to be sent automatically to all servers, then try the following:


proxycfg -d -p " " "*"

i.e, a blank (one space) name for the proxy server. And "*" for the bypass list means bypass the proxy for all sites. Or you could just give the name of the particular target server in the bypass list if that's all you care about.

Why does my SQLXML 3.0 setup fail when I have MDAC 2.7?

Bryant Likes — Thu, 15 Aug 2002 03:42:46 GMT

Answer provided by Amar Nalla.

The SQLXML 3.0 install may fail on machines that have MDAC 2.7 installed on them. If this happens you can check the value for the following registry key:


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\FullInstallVer

It should be set to the version of MDAC that you have installed. You can set this value and the install should run normally.

NOTE: Please edit the registry with care and only if you understand what you're doing.

How To: Get the details of an exception with the SQLXML Managed Classes.

Bryant Likes — Fri, 09 Aug 2002 18:26:51 GMT

A common problem when working with the .Net SQLXML Managed Classes is not being able to get the details of the exceptions when they occur. In order to get the details you will need to use a try-catch block of code. Below is an example of this.

[C#]
...
try
{
   // Perform your execute here
   cmd.ExecuteToStream(strm);
}
catch(SqlXmlException sxe)
{

   //in case of an error, this prints error returned.
   Console.WriteLine(sxe.ToString());
}
...

The code above writes the error to the console. If you're using ASP.Net you could also do something like the following.


Response.Write(sxe.ToString());

You can also throw a new exception as shown by the example posted by Oleg Chetverikov.


throw new Exception(sxe.ToString());

The important thing is that you get the details of the exception so that you can debug the problem.

Why doesn't bulk loading work in .Net?

Bryant Likes — Tue, 30 Jul 2002 23:32:26 GMT

Answer provided by weipingle

Bulkload works well in VB since VB uses single thread apartment by default. In C#, the same code will cause the following exception:

 
System.InvalidCastException: QueryInterface for interface
SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed

In order to make bulkload work in C#, you need to make sure that the thread mode is STA by :

 
System.Threading.Thread.CurrentThread.ApartmentState = System.Threading.ApartmentState.STA;

The bulkload code is simple:

 
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class objXBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
 
objXBL.ConnectionString = "Provider=sqloledb;server=server;database=db;uid=id;pwd=password";
 
objXBL.ErrorLogFile = "SQLXML3Books.errlog";
 
objXBL.KeepIdentity = false;
 
objXBL.Execute("Books.xsd", "Books1.xml");

The following code was provided by Gjalt Wijma which demonstrates how to use threading with bulk loading.

 
// Create a new thread 
Thread bulkLoad = new Thread( new ThreadStart( LoadData ) ); 
bulkLoad.ApartmentState = ApartmentState.STA; 
bulkLoad.Start(); 
 
// Load data 
public void LoadData() 
{ 
    // Create new bulk load object SQLXMLBulkLoad3Class 
    objXBL = new SQLXMLBulkLoad3Class(); 
    // Set connection string 
    objXBL.ConnectionString = "..." 
    // Set log file 
    objXBL.ErrorLogFile = ".." 
    // Set keep identity to false 
    objXBL.KeepIdentity = false; 
    // Execute bulk load 
    objXBL.Execute("Books.xsd", "Books1.xml"); 
}

How can I get an element with a CDATA section?

Bryant Likes — Fri, 19 Jul 2002 18:15:47 GMT

Using FOR XML EXPLICIT allows you to generate CDATA sections using the CDATA directive. However, many time you may want to have an element that contains the CDATA. To do this you can use the following example.


select  1 as Tag,
 null as Parent,
 employeeID as [emp!1!E_ID!element],
 '<![CDATA[' + firstname + ']]>' as [emp!1!E_name!xml]
from  employees
for xml explicit

By using the XML directive in combination with the added text you get the desired result.

How can I create recursive XML?

Bryant Likes — Mon, 29 Apr 2002 08:01:10 GMT

You can do this several ways:
With XSL: http://sqlxml.org/faqs.aspx?55
With XSD: http://sqlxml.org/faqs.aspx?63
With SQL: http://sqlxml.org/faqs.aspx?53

Tutorial: Using SQLXML Templates with ASP.Net

Bryant Likes — Mon, 29 Apr 2002 07:46:02 GMT

I wrote this as a tutorial for the DevHood website. The site is a developer website for students. Click the link below to view the tutorial.

Using SQLXML Templates with ASP.Net

How can return XML from a stored procedure using VB?

Bryant Likes — Wed, 24 Apr 2002 04:38:44 GMT

I've gotten quite a few requested for an example using a stored procedure instead of a template. So this example uses a regular stored procedure.

Create a new EXE project in VB.
Add references to MSXML 4.0 and ADO 2.6
Create the stored procedure listed below.
Copy the code below into your form's code. Be sure to change the connection string.

Stored Procedure


create proc employee_get
(
	@id int
)
as

	select 	FirstName, 
		LastName, 
		Title,
		Region
	from 	employees
	where	employeeid = @id
	for xml auto

go

VB Code


Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
   
Set oDom = New DOMDocument40
   
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=; " & _
    "User ID=; Password=; Database=Northwind"
       
oCmd.CommandText = "employee_get"
oCmd.CommandType = adCmdStoredProc
    
Set oPrm = New Parameter
oPrm.Name = "@id"
oPrm.Value = "1"
oPrm.Type = adInteger
oPrm.Size = 4
oCmd.Parameters.Append oPrm
        
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "c:\temp\results.xml"
    
Unload Me

Other Resources:
Returning XML in VB with a template

HOWTO: Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client

HOWTO: Retrieve XML Data with a Template File from a Visual Basic Client

Why do I get a "queries not allowed" error?

Bryant Likes — Tue, 23 Apr 2002 23:03:37 GMT

Check the box that says "Allow URL queries" in the Virtual Directory Management tool.

Virtual Directory Properties Dialog Box (Settings Tab)

How can I insert parent-child foreign keys that are autogenerated?

Bryant Likes — Tue, 23 Apr 2002 22:57:53 GMT

Answer provided by Avner Aharoni.

This is not currently supported by SQLXML 3.0. It should be supported in the next version of SQLXML.

How can I prevent special characters from being encoded?

Bryant Likes — Tue, 23 Apr 2002 22:51:54 GMT

There are several directives that you can use with FOR XML (see BOL for complete list).

Below is a short for xml explicit query that shows how content can be either encoded or not encoded.

 
select 1 as Tag, null as Parent,
 '<hi>' as [example!1!notencoded!xml],
 '<hi>' as [example!1!encoded!element]
for xml explicit

The results of this query are:

 
<example><notencoded><hi></notencoded><encoded>&lt;hi&gt;</encoded></example>

Notice how the encoded results have been encoded.

NOTE: This will not work for content that is already encoded. For content that stored in its encoded format you will need to do some kind of replace or use XSL.

How To: Solve "Ambiguous delete, unique identifier required" errors

Bryant Likes — Tue, 23 Apr 2002 22:46:27 GMT

Answer provided by Avner Aharoni

When running an updategram to delete a record you may receive an error that says "Ambiguous delete, unique identifier required Transaction aborted." This error may be caused by your database design and might have nothing to do with ambiguous identifiers.

Make sure that your updategram is not trying to delete records that are part of a join or records that have foriegn keys. If you are still having problems run the SQL Profiler and see what is being generated by your updategram. From the query that is generated you should be able to determine why it is not able to run.

How To: Union all with different data types using explicit

Bryant Likes — Tue, 23 Apr 2002 07:43:49 GMT

Solution provided by Denis Lienard.

If you want to union two queries that have a column with different data types, you can use this trick to avoid casting the data as a different type. The SQL below is a simple example that demonstrates this technique.


CREATE TABLE test_one
(
 one_id    numeric
)
CREATE TABLE test_two1
(
 two1_id    numeric,
 one_id  numeric,
 two1_ntext ntext
)
CREATE TABLE test_two2
(
 two2_id    numeric,
 one_id  numeric,
 two2_num numeric
)

insert into test_one values(1)
insert into test_one values(2)

insert into test_two1 values(1, 1, N'some text ...' )
insert into test_two1 values(2, 2, N'some text ...' )

insert into test_two2 values(1, 1, 250 )
insert into test_two2 values(2, 2, 3000)

select
 1   as Tag,
   NULL  as Parent,
 one_id as [ONE!1!ONE_ID],
 NULL  as [TWO!2!TWO_ID],
 NULL  as [TWO!2!ONE_ID],
 NULL  as [TWO!2!TYPE],
 NULL  as [TWO!2!!cdata], /*for ntext*/
 NULL  as [TWO!2!!cdata] /*for numeric*/
from
 test_one

UNION ALL

select
  2,
  1,
      test_one.one_id,
  test_two1.two1_id,
  test_two1.one_id,
  N'NTEXT',
  test_two1.two1_ntext,
  null
from
 test_one
 INNER JOIN test_two1
  ON test_one.one_id = test_two1.one_id

UNION ALL

select
  2,
  1,
      test_one.one_id,
  test_two2.two2_id,
  test_two2.one_id,
  N'NUMERIC',
  null,
  test_two2.two2_num
from
 test_one
 INNER JOIN test_two2
  ON test_one.one_id = test_two2.one_id
ORDER BY
      [ONE!1!ONE_ID],
      [TWO!2!TWO_ID]

for xml explicit

DROP TABLE test_two2
DROP TABLE test_two1
DROP TABLE test_one

In this example it would not be possible to create this union without casting the ntext column as a varchar. By casting it as varchar you lose the large size of the ntext field which might be a problem. The solution is to specify two columns with the same name which will show up the same in the xml.

How can I return XML without the sql namespace declaration?

Bryant Likes — Thu, 11 Apr 2002 20:23:06 GMT

Instead of using something like:


<root xmlns:sql="urn:schemas-microsoft.com:xml-sql">
<sql:query>select * from table for xml auto</sql:query>
</root>

Use something like:


<root>
<sql:query xmlns:sql="urn:schemas-microsoft.com:xml-sql">
  select * from table for xml auto
</sql:query>
</root>

How To: Configuring SQLXML IIS

Bryant Likes — Wed, 13 Feb 2002 05:13:39 GMT

This tutorial provided by Dan Doris.

Configuring SQLXML IIS

As a note, most problems I've seen with people setting up a virtual directory in SQLXML, tends to be from either using the normal IIS snap-in instead of the SQLXML IIS Configuration utility or the standard IIS setup has been changed. Prior to setting up SQLXML you should verify your IIS installation is working correctly apart from SQLXML (a simple test--issue a request to http://localhost). SQLXML leverages the functionality of IIS, if IIS is not running correctly there will be no way for SQLXML to process your requests.

Additionally this document assumes you have read the on-line help for setting up a SQLXML Virtual Directory, within SQL Books On-Line or the respective web release. It is also assumed you are comfortable working with IIS and have a working knowledge of IIS.

What is needed to configure a VDir for SQLXML?

The minimum installation requires both the SQL Client tools and the version of SQLXML with the features you desire to use. IIS does not need be on the same machine as SQL Server.

I can't connect with the SQLXML Configuration on my local server with SQL.

There are a few things you need to ensure are installed and running. First IIS must be set up and running on the machine. Additionally, you must have ADMIN privileges from the currently logged on user account. Assuming you have a standard configuration for IIS, open an instance of Microsoft Internet Explorer and enter the following request:

http://localhost

If you do not see two web pages displayed, either IIS is not running, not installed, incorrectly configured, or you have made some change to the standard IIS configuration for the default web site. If IIS is not running, start it, or if IIS is not installed, install it.

If you didn't see the two pages display and you know for sure IIS is installed and running, then you should open the IIS configuration tool (for SQLXML configuration, I think this will be about time you should use this in respect to setting up SQLXML VDirs). I wish to STRONGLY warn about using any tool besides the SQLXML IIS Configuration tool to update, edit, or view SQLXML VDirs. Before proceeding from here you will need to know why your request to http://localhost didn't get processed as expected. For this you should turn on IIS logging for the default web site (or the webs site(s) you are having trouble with). View the results of the IIS log to see more information about your request(s).

I have the SQLXML Client tools installed on a remote server, and I can't connect to the remote IIS machine.

There are a couple of possible reasons for this. As the configuration information for SQLMXL is stored in IIS's metabase, you must have ADMIN privileges on the remote IIS machine. If you're logged in account does have ADMIN permission on the remote machine, but you're not able to connect, then you should go to the remote machine and ensure everything is running locally. Verify you are able to view the properties for the SQLXML VDirs locally and process requests as you'd expect. Assuming everything on the local machine works, but on the remote machine your request still fails, or you're not able to connect from remote SQLXML IIS Config tool, the next step is to ensure the server you're connecting to has the correct DCOM settings. To edit the DCOM settings for SQLXML run "dcomcnfg.exe" from the command line. In Windows XP it will start as the Computer Manage MMC, under "Component Services::Computers::MyComputer" you should see DCOM Settings. You'll want to edit the values for:

Microsoft SQL Virtual Directory Control ([1|2|...])

You will need to enable user permissions so they may access and launch the SQLVDIR.dll

My web site works, but whenever I issue a request to one of SQLXML Virtual directories it fails.

First, see what the actual error is, you can do this by turning off friendly error message from within Internet Explorer. This is found under Tools::Options::Advanced, and is a check box. Make sure it is unchecked.

A common problem here is a SQLXML VDir was edited or modified from IIS's MMC console. I would recommend using a new VDir for this test, one you know for certain is unique. Follow the steps as outlined for the "Nwind" sample in the help file. One side note, you will always see a SQLVDIR in the normal IIS, but you should never open it to either view or edit its properties.

If a permission error still persists, then open the properties for the SQLVDIR again. On the Data Source tab verify the drop down list for the database appear and that you see the databases you would expect to see. If not, take your log-in credentials and connect using the Query Analyzer. Once you are able to connect with Query Analyzer, ensure your credentials for the SQL VDir are the same, effectively both use the same code to connect to SQL.

If the connection still fails when you verify by connecting to the database with Query Analyzer, I would not suggest using Integrated security, especially if your SQLXML VDir will be accessed from an external client. You should not proceed from here until you are able to connect using Query Analyzer. Before proceeding it's assumed you are able to see the list of database from the data source tab and can connect from Query Analyzer.

If a "file not found" error occurs, then ensure the template or schema your request works against is there. If your schema includes other files ensure they exists as well. As templates and schemas are both XML, verify you are able to open them with Internet Explorer, and if not fix any errors you find. I would recommend taking any of the sample from SQL Books on-line, each of the samples in SQL help work, I tried everyone one when learning SQLXML.

One simple test I like to use requires turning on "Allow sql=..." queries (you should typically never have this set for a public web site), and then issuing the following query:

http://localhost/VirtrualRoot?sql=select @@version"

You should see something like:

Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
    May 23 2001 00:02:52
    Copyright (c) 1988-2000 Microsoft Corporation
    Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

* After this don't forget to turn off URL SQL queries! *

If you didn't see the expected results then verify what happened to the request using the IIS logs and that you are still able to connect with the same user credentials using Query Analyzer. You may want need to run SQL Profiler too to verify if the query was received by the SQL Server as expected.

One last problem, which you may rarely see. If your request fails with file not found or file permission errors. You may need to provide access on all the directories along the path to the final directory where your templates and schemas files are located.

For example your configuration is:

Virtual Name	OS File Path
Local Path for VDir	c:\WebSite
DBObject	n/a -- no path involved.
Template	c:\WebSite\VDirForWeb\Templates.
Schema	c:\WebSite\VDirForWeb\Schemas

Thus you http requests should be something like:

http://[localhost|www.website.com]/VDir/Template/Template.xml

When this condition occurs you would need to provide read permission for the following directories:

     c:\WebSite
     c:\WebSite\VDirForWeb
     c:\WebSite\VDirForWeb\Templates

I'm not able to add a Virtual Directory with the name I want even though I don't see a Virtual Directory with the same name listed in either the SQLXML Configuration tool or IIS.

There are instances when the Metabase may have an anomaly in it where a VDir is not visible and exists in the Metabase but is not viewable from the normal tools. To fix this problem you will need to get a copy of MetaEdit and use it to delete the virtual directory.

You can find the MetaEdit tool at:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q232068

Most every problem I've seen when configuring the SQLXML VDir if these steps are followed have resolved all the problems I've seen, or pointed to the direction needed to be taken to resolve the problem.

If you try these steps and the problem persists, provide detailed information for your specific setup (SQL, WR version, and any other software you think relevant), the configuration for each machine (OS, Service pack, IP's...), how many web sites on each IIS machine and the IP setting for each, which will you plan on running SQLXML on. Typically less information than this one can only guess, it is information you should have available, if you don't know or have the information neither will anyone else who may help you.

How can I preserve whitespace when I use OpenXML?

Bryant Likes — Wed, 13 Feb 2002 04:18:43 GMT

This is a fairly common question. One method is to wrap your text inside a CDATA tag:


declare @i int

exec sp_xml_preparedocument @i output, '
<root><letter_head><![CDATA[   123]]></letter_head></root>
'

select * from OpenXML(@i, 'root', 2) with(letter_head varchar(20))

exec sp_xml_removedocument @i

I recently came across a better solution. You can encode your spaces using the   character. This can be done when you create your XML or if you don't have control over the creation of the XML you can use the T-SQL replace function to replace spaces.


declare @i int

exec sp_xml_preparedocument @i output, '<root>
<test> space space  space   </test>
<test>&#160;space&#160;space&#160;&#160;space&#160;&#160;&#160;</test>
</root>'

select '"' + test + '"' from OpenXML(@i, 'root/test') with (test varchar(50) '.')

exec sp_xml_removedocument @i

This works better than the CDATA method. If you find a better solution let me know.

How To: Insert identity values using XML Bulk Load.

Bryant Likes — Thu, 31 Jan 2002 18:55:53 GMT

You can use XML Bulk Load to insert records into tables that use identity columns. Here is a simple example to demonstrate how this can be done. First create a table in your database.


CREATE TABLE Cust (
    CustomerID     int         IDENTITY PRIMARY KEY,
    CompanyName    varchar(20) NOT NULL,
    City           varchar(20) DEFAULT 'Seattle')
GO

Next create the schema for the table and save it. I have saved the schema as c:\samples\iden.xsd.


<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Customers" sql:relation="Cust" >
   <xsd:complexType>
     <xsd:sequence>
       <xsd:element name="CustomerID" type="xsd:integer" />
       <xsd:element name="CompanyName" type="xsd:string" />
       <xsd:element name="City"        type="xsd:string" />
     </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Then create some sample data to load into the database and save it.


<ROOT>
  <Customers>
    <CompanyName>Hanari Carnes</CompanyName>
    <City>NY</City> 
  </Customers>
  <Customers>
    <CompanyName>Toms Spezialitten</CompanyName>
    <City>LA</City>
  </Customers>
  <Customers>
    <CompanyName>Victuailles en stock</CompanyName>
  </Customers>
</ROOT>

Then to load the data use the following VBScript code.


Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.2.0")
    
oBL.ConnectionString = "provider=SQLOLEDB.1;server=(local)\VSdotNet;" & _
   "database=test;Trusted_Connection=Yes;"
oBL.KeepIdentity = False

oBL.Execute "c:\samples\iden.xsd", "c:\samples\iden.xml"

Be sure to modify the connection string with your own values.

The limitation on identity columns in XML Bulk Load is that you cannot use it with multiple tables. For instance, if you had an orders table that had a foreign key for the customers table, it would not be bulk loadable. This is a limitation of XML Bulk Loading.

Why can't I get valid XML from SQL Server?

Bryant Likes — Thu, 31 Jan 2002 03:30:00 GMT

This is probably one of the most common questions asked in the newsgroups. The most common causes of this problem are:

Attempting to retrieve the results of an XML query with a recordset.
Using Query Analyzer to get the results.
Connecting to SQL Server with an ODBC connection.

The correct method of retreiving XML from SQL Server is to use either streams with an SQLOLEDB connection or HTTP through the SQLXML virtual directories. To see an example of using streams take a look at two examples on this site: VB Example and ASP Example. Query Analyzer does not do very well with XML resultsets so you're better off not using it to check your XML results. ODBC also does not work because the SQLOLEDB provider is needed to stream the XML results.

How To: Create multiple element types in a schema based on a filter

Bryant Likes — Wed, 12 Dec 2001 20:31:49 GMT

The question was brought up of how multiple elements can be defined off of a single table based on a filter. For example, if you have a products table that all your products are stored in, how can you define both a Book element and a Music element. To answer this question we will use the Northwind database and create a list of Employees that is based on who they report to.

The two key items for this schema are the sql:limit-field and the sql:limit-value attributes. These attributes are defined in the SQLXML SDK under the XSD schemas section. The sql:limit-field is used to define which column you want to place a limit on; the sql:limit-value is used to defined what value you want to use to filter the table.

Here is the schema that I created to demonstrate this concept:


<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">


 <xsd:element name="employees" sql:is-constant="1">
  <xsd:complexType>
   <xsd:sequence>
    <xsd:element name="salesRep" 
	sql:relation="Employees" 
	sql:limit-field="ReportsTo" 
	sql:limit-value="2">
      <xsd:complexType>
       <xsd:sequence>
	 <xsd:element name="FirstName" type="xsd:string"/>
	 <xsd:element name="LastName" type="xsd:string"/>
       </xsd:sequence>
       <xsd:attribute name="EmployeeID" type="xsd:integer" />
      </xsd:complexType>
    </xsd:element>
    <xsd:element name="manager" 
	sql:relation="Employees" 
	sql:limit-field="ReportsTo" 
	sql:limit-value="5">
      <xsd:complexType>
       <xsd:sequence>
	 <xsd:element name="FirstName" type="xsd:string"/>
	 <xsd:element name="LastName" type="xsd:string"/>
       </xsd:sequence>
       <xsd:attribute name="EmployeeID" type="xsd:integer" />
      </xsd:complexType>
    </xsd:element>
   </xsd:sequence>
  </xsd:complexType>
 </xsd:element>
</xsd:schema>

The result of running this schema creates the list of employees. Those that report to "2" are created with a salesRep element; those that report to "5" are created with a manager element. Based on this example you should be able to create similar schema for such things as products.

Why aren't my error messages being returned?

Bryant Likes — Wed, 12 Dec 2001 19:46:25 GMT

Provided by Martyn Johnson.

If you're running into a problem where your error messages from SQL are not being returned, the problem may be with MDAC. This problem applies to machines running MDAC 2.6. Take a look at the following KB article for more information.

FIX: MDAC 2.6 SQLOLEDB Provider Running on Windows 2000 Returns No Error Text Description

What is required to use SQLXML on a remote server?

Bryant Likes — Wed, 12 Dec 2001 19:32:41 GMT

Provided by Dan Doris.

SQLXML may be installed on a web server running IIS only and may not have SQL Server 2000 installed locally. In this configuration the IIS machine will need the SQL Server 2000 Client utilities to be installed. The reason for the SQL Server 2000 Client utilities, they provide the communication layer needed (SQL DMO) between IIS machine and the remote SQL Server machine.

Depending on your specific setup you may have multiple IIS and SQL 2000 Servers within your organization that you will need to configure. Given a scenario when you have more than one IIS machine, you may want to configure any of your IIS's virtual directories from one IIS machine. Along with needing the SQL Client utilities, one additional item you will need to make sure of is the account login used to configure the remote SQLXML virtual directories has admin privileges on the remote IIS machine(s). This is more of a require from IIS, as SQLXML will need to access the remote IIS's metabase, thus the need admin privileges.

Tutorial: Extending SQLXML with XSL and Objects

Bryant Likes — Sun, 25 Nov 2001 05:56:21 GMT

This example is based on the Msgxml.com code. One of the features that was requested was to allow for users to have their passwords emailed to them. Instead of creating this type of function with ASP I decided to use XSL to demonstrate how this can be done.

If you have already created the Msgxml.com message board you can add this code to extend your own message board. You can also use this example to extend your own SQLXML application by making use of this type of coding.

First we are going to create an XML template. This template will return the user's email address and password. This template is very simple. If you're using the Msgxml.com message board you can name this file reminder.xml.


<?xml version="1.0"?>
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="reminder.xsl">

 <sql:header>
   <sql:param name="userID"/>
 </sql:header>
 
 <sql:query><![CDATA[
    
 select	userID,
		password
 from	users
 where	userID = @userID
 for xml auto
	
 ]]></sql:query>

</root>

This simply returns XML that contains the user's email and password. Next we will create the stylesheet. The XSL will contain the VBScript that is used to send an email. Normally you wouldn't think of XSL doing things like sending email, but you can extend XSL to do this type of work plus anything else that you put into your own COM DLLs.


<?xml version='1.0'  encoding="windows-1252"?>
<xsl:stylesheet version="1.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:vb="http://msgxml.com/vb"
>
                
<xsl:output method="html" version="4.0"/>

<msxsl:script language="VBScript" implements-prefix="vb"><![CDATA[
   
   Function SendMsg(userID, password)
   
	Dim oMg, oCf, oFds

	Set oMg = CreateObject("CDO.Message")
	Set oCf = oMg.Configuration
	Set oFds = oCf.Fields
                
	oFds("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	oFds("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
	oFds.Update                

	Set oFds = Nothing                

	With oMg   
		.From = "reminder@sqlxml.org"   
		.Subject = "Msgxml.com Password Reminder"   
		.TextBody = "Your msgxml.com password is: " & password 
		.To = userID  
		.Fields.Update   
		.send
	End With  
	
	SendMsg = "Your password has been sent to " & userID
	
	Set oMg = Nothing
   
   End Function
   
   
]]></msxsl:script>


<xsl:template match="/">

<xsl:choose>
<xsl:when test="root/users">

<html>
<head>
<title>Msgxml.com Reminder</title>
</head>
<body>

<p>
<xsl:value-of select="vb:SendMsg(string(root/users/@userID), string(root/users/@password))"/>
</p>

<p>
<input type="button" onClick="javascript:window.close();" name="btnClose" value="Close"/>
</p>

</body>
</html>
</xsl:when>
<xsl:otherwise>

<html>
<head>
<title>Error with Login</title>
<script language="JavaScript">
document.location.href="/getuser.asp?error=username not found";
</script>
</head>
</html>

</xsl:otherwise>
</xsl:choose>

</xsl:template>

</xsl:stylesheet>

So this XSL creates and sends an email message using CDO and ADO. You can also create your own COM DLLs in VB or C++ to extend your own applications. This adds a lot of functionality to SQLXML.

If you're using the Msgxml.com message board, save the template as reminder.xml and the stylesheet as reminder.xsl. Put them both in the sqlxml directory and add a new template mapping to reminder.xml. Then you also have to modify the getuser.asp to add the link.

First you need to add the following javascript function to the script section:


function sendPass()
{

	if (frmLogin.userID.value.length == 0)
	{
		alert('Please enter your email address');
		return;
	}
	
	document.location.href = '/msg/reminder.xml?userID=' + frmLogin.userID.value;
	return;

}

Next add the follow row to the table under the password row:


<tr>
<td></td>
<td></td>
<td class="smallb">
  &nbsp;&nbsp;
  <a href="#" onClick="sendPass();">Click here if you forgot your password.</a>
</td>
</tr>

You can test this out at Msgxml.com.

What range of characters can be used in XML?

Bryant Likes — Fri, 23 Nov 2001 02:21:46 GMT

Many times errors occur when attempting to import or export data as XML. Invalid characters are a common nusance. For information on what range of characters if valid for XML take a look at the following page:

http://www.w3.org/TR/2000/REC-xml-20001006#charsets

Can I store my XSL in a stored procedure?

Bryant Likes — Fri, 23 Nov 2001 02:11:27 GMT

This is kind of an interesting question that I had some fun with. The answer is yes you can store your XSL stylesheet in a stored procedure. This may have some value in that you can manage both your XML output and your XSL stylesheets in one place.

This first example is more of an interesting method than a pratical one. I am gonig to call the stored procedure that holds the XSL from an XML template. A more likely example would be to call the stored procedure from ADO. Any how this does demonstrate how you can manipulate your XSL prior to using it to transform your XML. In this example I just return the XSL as it is, but you could write a stored procedure (or ASP) to create XSL on the fly. Below is the stored procedure with the embedded XSL:


create proc sp_empXSL
as

select '
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                version="1.0">
                
<xsl:output method="html" version="4.0" encoding="ISO-8859-1"/>

<xsl:template match="/">

 <html>
 <head>
 <title>View/Edit Employees Sample</title>
 </head>
 <body>


 <ul>
 
 <form name="frmSelect">

Choose an Employee:
 <select name="empID" onchange="frmSelect.submit();">
 <option/>
<xsl:for-each select="root/employee">
 <option>
  <xsl:attribute name="value">
    <xsl:value-of select="@employeeID"/>
  </xsl:attribute>
  <xsl:if test="@selected">
    <xsl:attribute name="selected"/>
  </xsl:if>
  <xsl:value-of select="@firstName"/>&#160;<xsl:value-of select="@lastName"/>
 </option>
</xsl:for-each>
 </select>

 </form>

<xsl:for-each select="root/employee[@selected=''true'']">
 <form name="frmEdit" method="POST">
 <xsl:attribute name="action">
 empx.xml?empID=<xsl:value-of select="@employeeID"/>
 </xsl:attribute>

   <input type="hidden" name="edit" value="true"/>
   <input type="hidden" name="empID">
    <xsl:attribute name="value"><xsl:value-of select="@employeeID"/></xsl:attribute>
   </input>

  FirstName:&#160;
   <input name="firstName" type="text">
    <xsl:attribute name="value"><xsl:value-of select="@firstName"/></xsl:attribute>
   </input><br/>

  LastName:&#160;
   <input name="lastName" type="text">
    <xsl:attribute name="value"><xsl:value-of select="@lastName"/></xsl:attribute>
   </input><br/>

  Title:&#160;&#160;&#160;
        &#160;&#160;&#160;
        &#160;&#160;&#160;&#160;
   <input name="title" type="text">
    <xsl:attribute name="value"><xsl:value-of select="@title"/></xsl:attribute>
   </input><br/>

   Birthday:&#160;&#160;&#160;&#160;
   <input name="birthday" type="text">
    <xsl:attribute name="value"><xsl:value-of select="@birthday"/></xsl:attribute>
   </input><br/>

   <input type="submit" name="submit" value="Save"/>

 </form>
</xsl:for-each>

 </ul>

 </body>
 </html>
  
</xsl:template>  

</xsl:stylesheet>'

return 

go

The stylesheet is actually from a different example. It just takes some employee XML and creates an HTML form to edit/view the information. Now to get the XSL out of the stored proc we will use an XML template in this example. You could easily call the procedure using ADO streams to load it into a DOMDocument to transform some XML. Here is the template empXSL.xml:


<?xml version="1.0" encoding="ISO-8859-1"?>
<sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  exec sp_empXSL
</sql:query>

The template is pretty simple. It just calls the stored proc and creates a valid XSL stylesheet for us to use. Now we just add this to our final template (empx.xml) which makes the actual query and generates the XML to be transformed:


<?xml version="1.0" encoding="ISO-8859-1"?>
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="http://localhost/nwind/tp/empxsl.xml">

 <sql:header>
   <sql:param name="empID"/>
   <sql:param name="firstName"/>
   <sql:param name="lastName"/>
   <sql:param name="title"/>
   <sql:param name="birthday"/>
   <sql:param name="edit"/>
 </sql:header>
 
 <test><sql:query>select @lastName</sql:query></test>

 <sql:query>

	if (@edit='true') begin

		update employees
		set firstName = @firstName,
		lastName = @lastName,
		title = @title,
		birthdate = @birthday
		where employeeID = @empID
	end

	select	firstName,
		lastName,
		title,
		employeeID,
		convert(varchar(12), birthdate, 101) birthday,
		case when @empID = employeeID then 'true' else NULL end as selected
	from	employees employee
	for xml auto

 </sql:query>
 
</root>

It is important to notice that I reference the XSL with the full URL and not just the file name. This is required for SQL to run the empXSL template through the ISAPI dll instead of just loading it as it is.

If you're not sure how to use templates take a look at the BOL info.