Simplify the Creation of XML from SQL Server Data

  • Hugh Lynch

    SSC Enthusiast

    Points: 116

  • Bert De Haes

    Hall of Fame

    Points: 3073

    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

    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

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

     

    Jason Hawryluk

  • Bob Monahon

    Ten Centuries

    Points: 1282

    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: 47779

    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

    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

    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: 47779

    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: 2878

    I couldn't get it either.



    Steve Miller

  • Bert De Haes

    Hall of Fame

    Points: 3073

    I couldn't get it either ...

  • hawryluk

    SSC Journeyman

    Points: 78

    What is this site? I get timeouts as well.

  • Paul -244830

    Mr or Mrs. 500

    Points: 501

    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

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

  • RyanRandall

    SSChampion

    Points: 13623

    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.

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

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