Formatting xml using sp_makewebtask

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

Old Forums

CLOSED - General SQL Server

Formatting xml using sp_makewebtask

Author

Topic

gailharbin
Starting Member

1 Post

Posted - 2006-05-24 : 09:54:01

Hello all!

I am trying to use sp_makewebtask to create an XML file. Any suggestions would be GREATLY appreciated!!

In Query Analyzer:

use northwind
go

exec sp_makewebtask
@outputfile = 'c:\testxml\ShippersGail.xml',
@query = 'Select * from shippers for xml auto',
@templatefile = 'c:\testxml\shippers.tpl'

Shippers.tpl:
<?xml version="1.0" encoding="UTF-8"?>
<Shippers>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Shippers>

I am getting this output:

<?xml version="1.0" encoding="UTF-8" ?>
<Shippers>
<shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" />
<shippers ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" />
<shippers ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" />
</Shippers>

I am wanting to get this:

<?xml version="1.0" encoding="UTF-8" ?>
<Shippers>
<ShipperID>1</ShipperID>
<CompanyName>Speedy Express</CompanyName>
<Phone>(503) 555-9831</Phone>
</Shippers>
<Shippers>
<ShipperID>2</ShipperID>
<CompanyName>United Package</CompanyName>
<Phone>(503) 555-3199</Phone>
</Shippers>
<Shippers>
<ShipperID>3<?ShipperID>
<CompanyName>Federal Shipping</CompanyName>
<Phone>(503) 555-9931</Phone>
</Shippers>

What can I do?

Gail Harbin

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-05-25 : 14:06:29

Gail,

According to BOL:

"Specify a complete row format to produce a more precise layout. Use the <%begindetail%> and <%enddetail%> markers and define a complete row format between them with <TR>, </TR>, <TD>, and </TD> HTML tags. For each column to be displayed in the result set, insert the <%insert_data_here%> marker."

So, you simply need to change your template file. Add an element for each column to be displayed (eg., <ShipperID><%insert_data_here%></ShipperID>). If I read BOL right, that should do what you need.

Just out of curiosity, why are you using sp_makewebtask instead of FOR XML EXPLICIT? sp_makewebtask is designed for HTML output, not XML.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources