XML Child Nodes 'loops' [RESOLVED]
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):
Code:
<?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:
get xml data Code:
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:
Possible solution? Code:
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.
Re: XML Child Nodes 'loops'
I'm a bit confused as to what you want. You can do this to get the distribution entries:
vb.net Code:
Dim distributions = awsFeed...<order>...<distributions>...<distribution>
For Each element As XElement In distributions
'//something
Next
But maybe you are after something else?
Re: XML Child Nodes 'loops'
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;
Code:
<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:
Code:
<Order>
<id>12345</id>
<name>VBForums</name>
<Distributions>
<Distribution>
<test>5678<test>
</Distribution>
<Distribution>
<test>8765<test>
</Distribution>
</Distributions>
</Order>
Thanks once again.
Re: XML Child Nodes 'loops'
Should be pretty much self-explanatory:
vb.net Code:
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.
Re: XML Child Nodes 'loops'
I usually create a user defined function to convert the xml into a table variable, then use the table variable as needed.
Code:
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
Code:
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)
Re: XML Child Nodes 'loops'
Thank you forever guys :) both answers helped me a lot!!
Great community this!
