Simplify the Creation of XML from SQL Server Data

Question

Post reply

Simplify the Creation of XML from SQL Server Data

Hugh Lynch

SSC Enthusiast

Points: 116
More actions
September 19, 2005 at 12:35 pm

#165328

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hlynch/simplifythecreationofxmlfromsqlserverdata.asp
Here is the download:
http://tech.rssgroup.com/shapes/RSSWebShapes.zip
and at
http://www.hughlynch.com/shapes/RSSWebShapes.zip

Viewing 15 posts - 1 through 15 (of 19 total)

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

Bert De Haes Hall of Fame Points: 3081 More actions · Answer 1

Great article.

I would really like to see how this is implemented, but could not find the download ?

Paul -244830 Mr or Mrs. 500 Points: 501 More actions · Answer 2

Perfect timing on this article too. I am about to implement some AJAX code and was looking for the right solution for generating the XML output from SQL.

The article is well written except I could not find a download link for the samples and the link at the bottom of the article to a Shape testing server is http://localhost/

Thanks for the article.

hawryluk SSC Journeyman Points: 78 More actions · Answer 3

Loved the article, but none of the links work, and no download to try it.

Jason Hawryluk

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 4

I agree - the article was great.

One link does work: http://tech.rssgroup.com/shapes/

Re: "The accompanying download includes the binary and configuration files necessary to experiment with shapes on your own." There's no download available with the article -- did you (or the folks at SQLServerCentral) forget to include it?

Regards, BobM

Regards,

Bob Monahon

philcart SSC-Forever Points: 47794 More actions · Answer 5

After just resorting to VBScript to create an XML file, I too would love to see the download.

--------------------
Colt 45 - the original point and click interface

Todd Miller Old Hand Points: 305 More actions · Answer 6

I to would like to test this code and wish there was a link to download the files. I could use this I believe.

Hugh Lynch SSC Enthusiast Points: 116 More actions · Answer 7

please find the download at:

http://tech.rssgroup.com/shapes/RSSWebShapes.zip

and a sample site at

http://tech.rssgroup.com/shapes

thanks for the interest! let me know if you have any installation troubles or further questions.

cheers,

Hugh

philcart SSC-Forever Points: 47794 More actions · Answer 8

Anyone had any success at getting to the tech.rssgroup.com website?

I've tried from my work computer and home computer and just get connection timeout errors.

--------------------
Colt 45 - the original point and click interface

Undebtedly SSCrazy Points: 2938 More actions · Answer 9

Undebtedly

SSCrazy

Points: 2938

October 27, 2005 at 11:30 pm

#600801

I couldn't get it either.

Steve Miller

Bert De Haes Hall of Fame Points: 3081 More actions · Answer 10

Bert De Haes

Hall of Fame

Points: 3081

October 27, 2005 at 11:51 pm

#600803

I couldn't get it either ...

hawryluk SSC Journeyman Points: 78 More actions · Answer 11

hawryluk

SSC Journeyman

Points: 78

October 28, 2005 at 1:47 am

#600815

What is this site? I get timeouts as well.

Paul -244830 Mr or Mrs. 500 Points: 501 More actions · Answer 12

Great, I got the attachments! Thanks.

This is a great solution to the problem. Many solutions include offloading the XML creation to SQL server, which just adds more stress to a single point of failure for most websites and applications. Also, it makes the SQL server output higher by adding XML tags and such at the SQL end. I dont like adding anything to SQL server I dont have to.

The one drawback (and it is a big one) is that you do not supply the source for the DLL. This is actually something which could be added to a good opensource project. If I dare use this DLL in a project for production, I have no guarantee that it will work in future versions of .net and if there is a bug in the library, I have no way to fix it myself or ask someone else to fix it. If this were open sourced or even made available for purchase from a company that would be around a while, that would be great and I would start using it immediately.

Paul Perrick

hawryluk SSC Journeyman Points: 78 More actions · Answer 13

I'm still getting timeouts, is this a browser address?

RyanRandall SSChampion Points: 13623 More actions · Answer 14

Good article - thanks Hugh. A shame about the download, but I'm sure you'll get that fixed.

In case anyone's not familiar with the FOR XML clause, here's how you can get XML results equivalent to the results in the article. As is so often the case, the method you choose will depend on your requirements, and the method in the article is a very useful one to add to the toolbox.

Okay, here we go. Both of these stored procedure calls...

exec dbo.XmlAuto_EmployeeActivity 6, '1 Jan 1997', '31 Jan 1997'

exec dbo.XmlExplicit_EmployeeActivity 6, '1 Jan 1997', '31 Jan 1997'

...give...

</Order>

</Customer>

</Order>

</Customer>

</Employee>

And here's the code for the 2 stored procedures...

create procedure dbo.XmlAuto_EmployeeActivity

@employeeID int

, @startDate smalldatetime

, @endDate smalldatetime as

select

Employee.EmployeeID, LastName, FirstName,

Customer.CustomerID, CompanyName,

[Order].OrderID, OrderDate,

ProductID, UnitPrice, Quantity, Discount

from

Employees as Employee

inner join Orders as [Order] on Employee.EmployeeID = [Order].EmployeeID

inner join Customers as Customer on Customer.CustomerID = [Order].CustomerID

inner join [Order Details] as Item on Item.OrderID = [Order].OrderID

where

Employee.EmployeeID = @employeeID and orderDate between @startDate and @endDate

for xml auto

---------------------------------------------------------------------------------------------

create procedure dbo.XmlExplicit_EmployeeActivity

@employeeID int

, @startDate smalldatetime

, @endDate smalldatetime as

--Put relevant orders into a table variable

declare @tblOrders table (OrderID int primary key)

insert @tblOrders

select orderID from orders

where employeeId = @employeeId and orderDate between @startDate and @endDate

--Header

select

null as 'tag', null as 'parent', --

null as 'Employee!1!EmployeeID', null as 'Employee!1!LastName', null as 'Employee!1!FirstName', --Employee

null as 'Customer!2!CustomerID', null as 'Customer!2!CompanyName', --Customer

null as 'Order!3!OrderID', null as 'Order!3!OrderDate', --Order

null as 'Item!4!ProductID', null as 'Item!4!UnitPrice', null as 'Item!4!Quantity', null as 'Item!4!Discount' --Item

where 0 = 1

union

--Employee

select 1, null, /**/ EmployeeID, LastName, FirstName, /**/ null, null, /**/ null, null, /**/ null, null, null, null

from Employees where employeeID = @employeeId

union

--Customer

select 2, 1, /**/ @employeeId, null, null, /**/ CustomerID, CompanyName, /**/ null, null, /**/ null, null, null, null

from Customers where customerID in (select customerID from orders where orderID in (select orderID from @tblOrders))

union

--Order

select 3, 2, /**/ @employeeId, null, null, /**/ CustomerID, null, /**/ OrderID, OrderDate, /**/ null, null, null, null

from orders where orderID in (select orderID from orders where orderID in (select orderID from @tblOrders))

union

--Item

select 4, 3, /**/ @employeeId, null, null, /**/ CustomerID, null, /**/ d.OrderID, null, /**/ ProductID, UnitPrice, Quantity, Discount

from [Order Details] d inner join Orders o on o.orderID = d.orderID where d.orderID in (select orderID from @tblOrders)

order by 'Employee!1!EmployeeID', 'Customer!2!CustomerID', 'Order!3!OrderID', 'Item!4!ProductID'

for xml explicit

---------------------------------------------------------------------------------------------

Regards,

Ryan

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.