Create Nested XML from Stored Procedure

Hi,

I have a stored prcedure that returns the data shown in results.txt (example).

I want to create a nested xml document from this that looks like this...

<Vehicles>
  <make name="ALFA ROMEO" qty="3">
    <model name="159" qty="2" priceFrom="13999.00" priceTo="16999.00">
      <derivative name="1.9 JTDM 16v Lusso 5dr" qty="1" priceFrom="13999.00" priceTo="13999.00" />
      <derivative name="1.9 JTDM 16V TI 4dr" qty="1" priceFrom="16999.00" priceTo="16999.00" />
    </model>
    <model name="SPIDER" qty="1" priceFrom="15499.00" priceTo="15499.00">
      <derivative name="2.2 JTS 2dr [Leather]" qty="1" priceFrom="15499.00" priceTo="15499.00" />
    </model>
  </make>
  <make name="AUDI" qty="27">
    <model name="A3" qty="9" priceFrom="12599.00" priceTo="19599.00">
      <derivative name="1.6 SE 3dr" qty="1" priceFrom="14999.00" priceTo="14999.00" />
      <derivative name="1.6 SE 5dr" qty="1" priceFrom="13599.00" priceTo="13599.00" />
      <derivative name="1.6 Special Edition 5dr" qty="1" priceFrom="12599.00" priceTo="12599.00" />
      <derivative name="1.8 T FSI 2dr S Tronic [Leather]" qty="1" priceFrom="19599.00" priceTo="19599.00" />
      <derivative name="1.9 TDI SE 5dr" qty="1" priceFrom="15499.00" priceTo="15499.00" />
      <derivative name="1.9 TDi Special Edition 5dr" qty="1" priceFrom="13699.00" priceTo="13699.00" />
      <derivative name="1.9 TDIe SE 5dr" qty="3" priceFrom="15999.00" priceTo="15999.00" />
    </model>
    <model name="A4" qty="18" priceFrom="15999.00" priceTo="20999.00">
      <derivative name="1.8T FSI 160 SE 4dr" qty="1" priceFrom="17599.00" priceTo="17599.00" />
      <derivative name="1.8T FSI 160 SE 4dr Multitronic" qty="1" priceFrom="17999.00" priceTo="17999.00" />
      <derivative name="2.0 TDI 143 Executive SE 4dr" qty="1" priceFrom="20999.00" priceTo="20999.00" />
      <derivative name="2.0 TDI 143 SE 4dr [New Model]" qty="3" priceFrom="19599.00" priceTo="19599.00" />
      <derivative name="2.0 TDI 143 SE 4dr Multitronic [New Model]" qty="4" priceFrom="18999.00" priceTo="19999.00" />
      <derivative name="2.0 TDI 143 SE 5dr Multitronic" qty="6" priceFrom="19999.00" priceTo="19999.00" />
      <derivative name="2.0 TDi 2dr" qty="1" priceFrom="15999.00" priceTo="15999.00" />
      <derivative name="2.0 TDi Sport 2dr" qty="1" priceFrom="16999.00" priceTo="16999.00" />
    </model>
  </make>
 
I have managed to acheive it by using 3 different stored procedures, but it seems very inefficient (see code snippet)

Is there better a way a can transform the data.

I am using vb.net 3.5 and linq.

Thanks in advance....


Dim _xml As New XDocument
        Dim _x As New XDeclaration("1.0", "UTF-8", "yes")
        Dim xe As New XElement("Vehicles")

        For Each _p As usp_get_vehicle_makesResult In mpL.usp_get_vehicle_makes("car")
            Dim el As New XElement("make", New XAttribute("name", _p.make), New XAttribute("qty", _p.qty))

            For Each a As usp_get_vehicle_modelsResult In mpL.usp_get_vehicle_models("car", _p.make)
                Dim el2 As New XElement("model", New XAttribute("name", a.vModel), New XAttribute("qty", a.qty), New XAttribute("priceFrom", a.price_from), New XAttribute("priceTo", a.price_to))

                For Each d As usp_get_vehicle_derivativesResult In mpL.usp_get_vehicle_derivatives("car", _p.make, a.vModel)
                    Dim el3 As New XElement("derivative", New XAttribute("name", d.Vehicle_trim), New XAttribute("qty", d.qty), New XAttribute("priceFrom", d.price_from), New XAttribute("priceTo", d.price_to))

                    el2.Add(el3)
                Next

                el.Add(el2)
            Next

            xe.Add(el)
        Next

        _xml.Add(xe)

Open in new window

results.txt
TheDuffManAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SriVaddadiCommented:
You could use "For XML"  in your select clause so that your sp itself returns the xml without writing any code to generate xml

example

select * from <your table name>
for xml auto

check these links

http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx
http://articles.sitepoint.com/article/data-as-xml-sql-server
TheDuffManAuthor Commented:
Thanks SriVaddadi,
I have looked at this, but I can only get the data in to the format shown in results.txt.
I was hoping to be able to store the results and query / iterate through them to be able to create the qty (count) and priceFrom (min) and priceTo (max) fields for each level of the XML document, as in the sample xml...
SriVaddadiCommented:
May i know the below
1. Why u dnt prefer to have results from sp in xml? I know you said something above but i cld not get it . sorry
2.  Why do u want xml from result set?
3. where are u storing the results from the query.

Please give more details about your scenario and requirements. I could suggest you the solution.

As I understand, you need results set in one format and xml in a different format.

you are formatting the results in results.txt into xml as order by make, model, vtrm(derivative) and other columns go as attributes to one of the three.

make,model and vtrim go as tags and rest go as attributes.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

TheDuffManAuthor Commented:
Hi,
This is not my database, I have been given access to several stored procedures to get the data out, and I'm writing a web service to allow the data to be consumed by several websites.
The results are returned as xml from the webservice.
Hope that makes sense.
Thanks again.
Fernando SotoRetiredCommented:
Hi TheDuffMan;

Below code snippet show how it can be done. One issue I have is that in the following line :

<model name="159" qty="2" priceFrom="13999.00" priceTo="16999.00">

I do not know where you will pull priceFrom and priceTo for the model element.

Fernando
Imports System.Xml.Linq
Imports System.IO


' The list results holds the parsed values from the Results.txt file
Dim results As List(Of ResultInfo) = New List(Of ResultInfo)
' Read the text file and parse it
Using fileParser As New FileIO.TextFieldParser("results.txt")
    fileParser.TextFieldType = FileIO.FieldType.FixedWidth
    Dim Fieldwidth() As Integer = New Integer() {26, 76, 51, 255, 17, 17, -1}
    fileParser.SetFieldWidths(Fieldwidth)
    Dim currentRow As String()
    ' Read the two lineas of headder
    fileParser.ReadFields()
    fileParser.ReadFields()
    While Not fileParser.EndOfData
        currentRow = fileParser.ReadFields()
        Dim newRec As New ResultInfo
        newRec.Make = currentRow(0).Trim
        newRec.Model = currentRow(1).Trim
        newRec.Trim = currentRow(2).Trim
        newRec.Qty = Integer.Parse(currentRow(3).Trim)
        newRec.PriceFrom = Decimal.Parse(currentRow(4).Trim)
        newRec.PriceTo = Decimal.Parse(currentRow(5).Trim)
        newRec.Type = currentRow(6).Trim
        results.Add(newRec)
    End While
End Using

Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-8", "yes"), _
                            New XElement("Vehicles"))

Dim groupResults = From ri In results _
                   Group ri By ri.Make Into makeGroup = Group _
                   Select New With {.Make = makeGroup.First().Make, _
                                    .TotalQty = (From q In makeGroup _
                                                 Select q.Qty).Sum(), _
                                    .Models = From model In makeGroup _
                                              Group model By model.Model Into modelGroup = Group _
                                              Select New With {.Qty = modelGroup.Sum(Function(q As ResultInfo) q.Qty), _
                                                                .Modesls = modelGroup}}

For Each make In groupResults
    Dim mk As XElement = <make name=<%= make.Make %> qty=<%= make.TotalQty %>></make>
    For Each model In make.Models
        Dim md As XElement = <model name=<%= model.Modesls.First().Model %> qty=<%= model.Qty %>></model>
        For Each der As ResultInfo In model.Modesls
            Dim derivative As XElement = <derivative name=<%= der.Trim %> qty=<%= der.Qty %>
                                             priceFrom=<%= der.PriceFrom %> priceTo=<%= der.PriceTo %>></derivative>
            md.Add(derivative)
        Next
        mk.Add(md)
    Next
    xDoc.Root.Add(mk)
Next


Public Class ResultInfo
    Public Make As String
    Public Model As String
    Public Trim As String
    Public Qty As Integer
    Public PriceFrom As Decimal
    Public PriceTo As Decimal
    Public Type As String
End Class

Open in new window

TheDuffManAuthor Commented:
Hi Fernando,
Thanks, this looks great - is there a way to read the data stright from the stored procedure that I execute using linq - i.e save it to an array?
Thanks again
 
 
 
Fernando SotoRetiredCommented:
Most likely. I would need to know what the SP returns. Is it a single table? Of what type? Can you post the code where you call the SP?
TheDuffManAuthor Commented:
The results.txt shows a sample of the output from the store procedure.
At present the sp is store as a method in the linqDataContext dbml. I call it like...
 Dim l As New LinqDataContext
 For Each v As usp_get_all_vehiclesResult In l.usp_get_all_vehicles()
etc...
Does that make sense?
Fernando SotoRetiredCommented:
Can you post the structure of usp_get_all_vehiclesResult, field names and data types ?
TheDuffManAuthor Commented:
The stored procedure returns a flat table with the following fields

vMake nvarchar(25)
vModel nvarchar(50)
vTrim nvarchar(50)
qty int
price_from money
price_to money
vehicle_type nvarchar(3)
I've attached a screenshot of the results from SQL server manager.
Thanks again...

Capture.JPG
Fernando SotoRetiredCommented:
Hi TheDuffMan;

In the below code snippet I have removed the code that read from a file and replaced it with the result of the Linq query.

Fernando
Imports System.Xml.Linq
Imports System.IO


Dim l As New LinqDataContext
Dim results As IEnumerable(Of usp_get_all_vehiclesResult) = l.usp_get_all_vehicles()

Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-8", "yes"), _
                            New XElement("Vehicles"))

Dim groupResults = From ri In results _
                   Group ri By ri.vMake Into makeGroup = Group _
                   Select New With {.Make = makeGroup.First().vMake, _
                                    .TotalQty = (From q In makeGroup _
                                                 Select q.qty).Sum(), _
                                    .Models = From model In makeGroup _
                                              Group model By model.vModel Into modelGroup = Group _
                                              Select New With {.Qty = modelGroup.Sum(Function(q As usp_get_all_vehiclesResult) q.qty), _
                                                                .Modesls = modelGroup}}

For Each make In groupResults
    Dim mk As XElement = <make name=<%= make.vMake %> qty=<%= make.TotalQty %>></make>
    For Each model In make.Models
        Dim md As XElement = <model name=<%= model.Modesls.First().vModel %> qty=<%= model.Qty %>></model>
        For Each der As usp_get_all_vehiclesResult In model.Modesls
            Dim derivative As XElement = <derivative name=<%= der.vTrim %> qty=<%= der.qty %>
                                             priceFrom=<%= der.price_from %> priceTo=<%= der.price_to %>></derivative>
            md.Add(derivative)
        Next
        mk.Add(md)
    Next
    xDoc.Root.Add(mk)
Next

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TheDuffManAuthor Commented:
Inspirational help from Fernando.

Thanks
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.