XML Child Nodes 'loops' [RESOLVED]

[Booster77]

Hi guys,

I have a problem which supposedly is easy to fix but I simply can't make it happen. So I hope someone can help me. I'll try to explain my problem as clearly as possible.

I have a bunch of XML's that need to be put into an SQL database. One XML represents all orders for a certain department of a store. The problem (for me) is that in one XML there can be (and are) multiple orders (called distributions in my case.) The XML looks as follows (truncated):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<order><process_id>322867</process_id>
<key1>RET</key1>
<key2>40_134985</key2>
<currency_code>EUR</currency_code>
<inv_type>Gold Standard CM</inv_type>

<----Truncated part because it would become very long--->

<distributions>
<distribution>
<dist_key>BIP            </dist_key>
<dist_type>ITEM</dist_type>
<dist_amount>16.52</dist_amount>
<dtax_code>2</dtax_code>
<dtax_rate>0.19</dtax_rate>
<dist_segm2>840</dist_segm2>
<dist_segm3>8001</dist_segm3>
<dist_segm4>BIP</dist_segm4>
</distribution>
<distribution>
<dist_key>OPSLAG         </dist_key>
<dist_type>ITEM</dist_type>
<dist_amount>1.38</dist_amount>
<dtax_code>2</dtax_code>
<dtax_rate>0.19</dtax_rate>
<dist_segm2>840</dist_segm2>
<dist_segm3>8001</dist_segm3>
<dist_segm4>OPSLAG</dist_segm4>
</distribution>
</distributions>

So in this example I have two 'distributions'. Now I can get all data out of the XML fine, which I do like this:

awsFeed = XDocument.Load(fileName)
 Dim orders = From order In awsFeed...<order> _
     Select process_id = order.Element("process_id").Value, _
     trx_date = order.Element("trx_date").Value, _ (etc.. etc..)

However, in this case my SQL input would have to result in 2 entries, because there are 2 orders. Both entries would have the same data, except for different entries. I know how to post to SQL, that's not the problem. My real problem is, how do I 'loop' it through the XML. I think i'm looking for something like:

For each 'distribution' in orders -> put into array.

Or something like that. After that I could query the array and put it in the database. I hope I'm making it clear enough. If anyone could help me I would be so grateful because I've been stuck on this for days now and I although I find many tutorials, I don't seem to understand any of them, so if anyone could explain it in my own code that would be awesome, thank you in advance.

[ForumAccount]

I'm a bit confused as to what you want. You can do this to get the distribution entries:

Dim distributions = awsFeed...<order>...<distributions>...<distribution>
For Each element As XElement In distributions
    '//something
Next

But maybe you are after something else?

[Booster77]

Thank you, I was afraid I was explaining it wrong. Mostly I just think I myself understand it wrong.

I have 1 XML, in that XML I have one 'node' <Order> which holds all data. But also in the same XML I have <Distribution>, which occurs more than once.

Every Distribution needs to be a seperate entry in my database, because every Distribution holds different data, but should include all the same data from <Order>. So, to show it in an example, if my XML would look like this;

<Order>
<id>12345</id>
<name>VBForums</name>
<Distributions>
   <Distribution>
       <test>5678<test>
   </Distribution>
   <Distribution>
       <test>8765<test>
   </Distribution>
</Distributions>
</Order>

Then I would have 2 Database entries which would BOTH hold the <id> and the <name> node, but with different Distribution data.

So to summon it up. I need a way to code:

For each <Distribution> in XML put DATA in (array??) with all of <order> and each individual <Distribution>

When I have found a way to do the above I would have an array/dataset that would look like this

Order[1] = 12345, VBForums, 5678
Order[2] = 12345, VBForums, 8765

Which I can then put in the database. First of all I think I'm asking for an incredibly wrong way, but second of all I'm having huge difficulties to explain what I need properly, my apologies for that.

So how do I get:

Order[1] = 12345, VBForums, 5678
Order[2] = 12345, VBForums, 8765

From:

<Order>
<id>12345</id>
<name>VBForums</name>
<Distributions>
   <Distribution>
       <test>5678<test>
   </Distribution>
   <Distribution>
       <test>8765<test>
   </Distribution>
</Distributions>
</Order>

Thanks once again.

[ForumAccount]

Should be pretty much self-explanatory:

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) Handles Button1.Click
 
        '//your xml
        Dim xml = "<order>" & _
                      "<id>12345</id>" & _
                      "<name>VBForums</name>" & _
                      "<distributions>" & _
                          "<distribution>" & _
                              "<test>5678</test>" & _
                          "</distribution>" & _
                          "<distribution>" & _
                              "<test>8765</test>" & _
                          "</distribution>" & _
                      "</distributions>" & _
                  "</order>"
 
        '//parse the xml into an XDocument
        Dim doc = XDocument.Parse(xml)
 
        '//get the id and name 
        Dim id = doc...<id>.Value()
        Dim name = doc...<name>.Value()
 
        '//select all the distribution info into anonyomous types
        Dim distributions = doc...<distributions>...<distribution> _
                               .Select(Function(d) New With {.Id = id, .Name = name, _
                                                             .Test = d...<test>.Value()})
 
        '//output the types
        For i = 0 To distributions.Count - 1
            Console.WriteLine("Order[{0}] = {1}, {2}, {3}", i, _
                              distributions(i).Id, distributions(i).Name, _
                              distributions(i).Test)
        Next
 
    End Sub
 
End Class

I'm not sure if you have any experience with anonymous types, but they are used here. You can also make a simple Structure that contains the fields that you want.

[wild_bill]

I usually create a user defined function to convert the xml into a table variable, then use the table variable as needed.

create FUNCTION [dbo].[udf_ParseDistributions]
    (
     @xmldata XML
    )
RETURNS @table table (
         dist_key char(15),
		 dist_type varchar(50), 
		 dist_amount money,
		 dtax_code int,
		 dtax_rate money,
		 dist_segm2 int,
		 dist_segm3 int,
		 dist_segm4 varchar(50)
		)
AS 
   BEGIN
	--create table variable for storing override information
    INSERT  INTO @table (dist_key,dist_type,dist_amount,dtax_code,dtax_rate,dist_segm2,dist_segm3,dist_segm4)
            SELECT  
					lines.dist.value('dist_key[1]', 'char(15)'),
                    lines.dist.value('dist_type[1]', 'varchar(50)'),
                    lines.dist.value('dist_amount[1]', 'money'),
                    lines.dist.value('dtax_code[1]', 'int'),
                    lines.dist.value('dtax_rate[1]', 'money'),
                    lines.dist.value('dist_segm2[1]', 'int'),
                    lines.dist.value('dist_segm3[1]', 'int'),
                    lines.dist.value('dist_segm4[1]', 'varchar(50)')
            FROM    @xmldata.nodes('/distributions/distribution') lines (dist)

	return

   END

declare @xmlData xml
set @xmlData = '<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<order>
<process_id>322867</process_id>
<key1>RET</key1>
<key2>40_134985</key2>
<currency_code>EUR</currency_code>
<inv_type>Gold Standard CM</inv_type>
</order>
<distributions>
<distribution>
<dist_key>BIP            </dist_key>
<dist_type>ITEM</dist_type>
<dist_amount>16.52</dist_amount>
<dtax_code>2</dtax_code>
<dtax_rate>0.19</dtax_rate>
<dist_segm2>840</dist_segm2>
<dist_segm3>8001</dist_segm3>
<dist_segm4>BIP</dist_segm4>
</distribution>
<distribution>
<dist_key>OPSLAG         </dist_key>
<dist_type>ITEM</dist_type>
<dist_amount>1.38</dist_amount>
<dtax_code>2</dtax_code>
<dtax_rate>0.19</dtax_rate>
<dist_segm2>840</dist_segm2>
<dist_segm3>8001</dist_segm3>
<dist_segm4>OPSLAG</dist_segm4>
</distribution>
</distributions>'

select * from [dbo].[udf_ParseDistributions](@xmlData)

[Booster77]

Thank you forever guys both answers helped me a lot!!

Great community this!