XML template query - not working!! 1

[msturges]

Hi,

I am currently struggling with trying to read information from a SQL Server 2000 database into an XML document.
I am trying to apply the template file query method.

My template file looks like this:

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?>                      
<root xmlns:sql=&quot;urn:schemas-microsoft-com:xml-
      sql&quot;>                              
<sql:query>                                                
	SELECT Code AS [Code], Description AS 
           [Description], Price AS [Price] 
	FROM Products 
	FOR XML AUTO, ELEMENTS
</sql:query>                                               
</root>

pretty simple, however, when I run the template file, by typing the following in the URL:

[URL unfurl="true"]http://10.34.49.2/AckXML/template/AckOrderForm1.xml[/URL]

my output looks like this :

  <?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; ?> 
- <root xmlns:sql=&quot;urn:schemas-microsoft-com:xml-sql&quot;>
  <sql:query>SELECT Code AS [Code], Description AS
     [Description], Price AS [Price] FROM Products FOR XML 
     AUTO, ELEMENTS</sql:query> 
  </root>

- not quite what I wanted!

What I am trying to achieve, is this :

  <?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?> 
- <root>
- <Products>
  <Code>0000000001</Code> 
  <Description>T Shirt</Description> 
  <Price>99.99</Price> 
  </Products>
- <Products>
  <Code>0000000002</Code> 
  <Description>Jacket</Description> 
  <Price>109.99</Price> 
  </Products>
- <Products>
  <Code>0000000003</Code> 
  <Description>Pants</Description> 
  <Price>89.99</Price> 
  </Products>
- <Products>
  <Code>0000000004</Code> 
  <Description>Shorts</Description> 
  <Price>79.99</Price> 
  </Products>
- <Products>
  <Code>0000000005</Code> 
  <Description>Boxer Shors</Description> 
  <Price>59.99</Price> 
  </Products>
- <Products>
  <Code>0000000006</Code> 
  <Description>Socks</Description> 
  <Price>29.99</Price> 
  </Products>
  </root>

If anyone can shed some light as to why it's not working, please do.

Much appreciated, Thanks,

Mike.

 

[SadeJade]

You can use vbScript to solve that problem, you make a variable that contains a string with the sql query and then execute it to a recordset... something like

Vsql="select field_name from table_name where condition"
Set db_rs = db_conn.execute(Vsql)

you must create the connection to the database (ODBC) and then you can create with the vbScript the XML strutured file!

take a look at

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

 

[martynj]

Have you configured SQL XML support for IIS ??

Templates will not work unless you have configured this !