SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simplify the Creation of XML from SQL Server Data


Simplify the Creation of XML from SQL Server Data

Author
Message
Bert De Haes
Bert De Haes
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 231
I couldn't get it either ...
hawryluk
hawryluk
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 1
What is this site? I get timeouts as well.



Paul -244830
Paul -244830
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 22

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
hawryluk
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 1
I'm still getting timeouts, is this a browser address?



RyanRandall
RyanRandall
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 4652

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...

<Employee EmployeeID="6" LastName="Suyama" FirstName="Michael">
<Customer CustomerID="GOURL" CompanyName="Gourmet Lanchonetes">
<Order OrderID="10423" OrderDate="1997-01-23T00:00:00">
<Item ProductID="31" UnitPrice="10.0000" Quantity="14" Discount="0.0000000e+000"/>
<Item ProductID="59" UnitPrice="44.0000" Quantity="20" Discount="0.0000000e+000"/>
</Order>
</Customer>
<Customer CustomerID="LAMAI" CompanyName="La maison d&apos;Asie">
<Order OrderID="10425" OrderDate="1997-01-24T00:00:00">
<Item ProductID="55" UnitPrice="19.2000" Quantity="10" Discount="2.5000000e-001"/>
<Item ProductID="76" UnitPrice="14.4000" Quantity="20" Discount="2.5000000e-001"/>
</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.
Hugh Lynch
Hugh Lynch
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

For those having trouble getting the download, I also posted it at:

http://www.hughlynch.com/shapes/RSSWebShapes.zip

I'm not sure what's up with tech.rssgroup.com; unfortunately, it's one of those awkward "works-for-me" situations. Sorry about the difficulty.

Ryan,

Thanks for the post. The FOR XML examples are excellent and if you wouldn't mind, I'd like to incorporate them into the article. I don't know if/when/where it will be published again, but nonetheless...

The examples make it fairly evident that even at this modest level of complexity, the FOR XML EXPLICIT syntax and construction leave a bit to be desired. On the other hand, the FOR XML AUTO seems simple enough, but provides very little control. Neither is much fun to work with in Query Analyzer and both have the property of burdening the database with work that could easily be farmed out.

I'll continue to monitor this thread for those wishing to give shapes a try.

-Hugh





RyanRandall
RyanRandall
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 4652

Hi Hugh,

I'm happy for you to use the FOR XML examples, so long as you give me credit

Good luck with future publishings!

All the best,
Ryan



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1826 Visits: 445

Hi Hugh.

Thank you very much for sharing such a wonderful utility with the community. It is obvious that you have thought a great deal about keeping the output flexible whilst not burdening the developer nor the server. I look forward to trying it out in the coming weeks (I'm absolutely flooded with work at the moment!).

Cheers!!





Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)

Group: General Forum Members
Points: 676 Visits: 307
Maybe I am missing something but this solution sounds a little bit like reinvention of the wheel. I typically bypass the XML in my applications by using typed datasets. Technically, this isn't bypassing XML but I don't think in terms of XML when I am using them. A typed dataset can be serialized to XML using the GetXML method (inherited from the DataSet class). If that isn't pretty enough then an XSL transformation should do the trick. I have used this with UI designers that needed XML data in Flash and they were able to work with the DataSet data without any transformation despite minimal programming experience.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Hugh Lynch
Hugh Lynch
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

I'm a big fan of wheels - I try to keep a bunch on hand so I'll always have one that fits!

What shapes offer is a way to produce precise, potentially complex XML simply and efficiently using store procs and generic invokers. One can create a rich, navigable web UI using shapes, XSL and no other code. The shape tester, using shpList, is a simple example of this.

Shapes also jive nicely with the trend toward background queries from client-side javascript.

Getting your first shape to work requires some non-trivial configuration of the generic components, but once that's done the incremental cost of new shapes is very modest.

-Hugh





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search