I'll try explain what i'm trying to achieve as best I can...
I'm generating order XML document in C# using XDocument based on data from a SQL Query. Some of the XML Elements & Attributes need only appear once but the Order Line information needs to be looped a number of times; for example if an order contains 3 items I need to generate three instances of the Order Line information, if an order contains 5 items I need to generate five instances of the Order Line information etc.
My XML structure will look like below:
Delivery information - no loop required
Invoice information - no loop required
Order Line information - loop required as per above
More delivery information (shipping type) - no loop required
To get the static information, i'm using a SQLDataReader and passing the information into a variable:
var example = reader["column_name"];
This works fine for when I don't need to loop. However i'm now at the stage where I want to create the loop for Order Line information.
Each Order Line will come as a seperate row in my SQL query, the row will contain all the static information (Delivery information, invoice information etc) as well as the individual Order Line information (SKU, Product Description etc).
Essentially what I think I need to do is a foreach loop for each row in the SQLDataReader, and populate variables to drop into the XML OrderLine section, however I'm not sure how to do this, nor is this the best approach.
I can create a static version of the OrderLine section easily enough, but how would I go about looping through the SQL rows to pass variables into it? My static OrderLine code is as below
new XElement("OrderLine", new XAttribute("TypeDescription", "Goods & Services"), new XAttribute("Action", "Add"), new XAttribute("TypeCode", "GDS"),
new XElement("LineNumber", new XAttribute("Preserve", "true"), "item_number then increment by 1"),
new XElement("Product",
new XElement("SuppliersProductCode", "VALUE"),
new XElement("Description", "VALUE")),
new XElement("Quantity", new XAttribute("UOMDescription", "VALUE"), new XAttribute("UOMCode", "VALUE"),
new XElement("Amount", "Value")),
new XElement("Quantity", new XAttribute("UOMDescription", "VALUE"), new XAttribute("UOMCode", "VALUE"),
new XElement("UnitPrice", "Value"))),
My full XML generate code looks like this:
new XElement("Order",
new XElement("OrderHead",
new XElement("Schema",
new XElement("Value", "3.05")),
new XElement("Parameters",
new XElement("Language", "en-GB"),
new XElement("DecimalSeperator", "."),
new XElement("Precision", "12.3")),
new XElement("OrderType", new XAttribute("Code", "PUO"), "Purchase Order"),
new XElement("OrderCurrency",
new XElement("Currency", new XAttribute("Code", "GBP"), "GB Pounds Sterling"))),
new XElement("OrderReferences",
new XElement("BuyersOrderNumber", new XAttribute("Preserve", "true"), incrementid_var)),
new XElement("Buyer",
new XElement("BuyerReferences",
new XElement("SupplierscodeForBuyer", "****VALUE****")),
new XElement("Contact",
new XElement("Name"))),
new XElement("Delivery",
new XElement("DeliverTo",
new XElement("Party", firstname_var + " " + lastname_var),
new XElement("Contact",
new XElement("Name", firstname_var + " " + lastname_var),
new XElement("Email", "****EMAIL ADDRESS****")),
new XElement("Address",
new XElement("AddressLine", street_var),
new XElement("AddressLine", city_var),
new XElement("AddressLine", region_var),
new XElement("PostCode", postcode_var),
new XElement("Country", new XAttribute("Code", countryid_var)))),
new XElement("DeliverFrom",
new XElement("Party", firstname_var + " " + lastname_var))),
new XElement("InvoiceTo",
new XElement("Party", firstname_var + " " + lastname_var),
new XElement("Address",
new XElement("AddressLine", street_var),
new XElement("AddressLine", city_var),
new XElement("AddressLine", region_var),
new XElement("PostCode", postcode_var),
new XElement("Country", new XAttribute("Code", countryid_var)))),
//Start Loop
new XElement("OrderLine", new XAttribute("TypeDescription", "Goods & Services"), new XAttribute("Action", "Add"), new XAttribute("TypeCode", "GDS"),
new XElement("LineNumber", new XAttribute("Preserve", "true"), "item_number then increment by 1"),
new XElement("Product",
new XElement("SuppliersProductCode", "VALUE"),
new XElement("Description", "VALUE")),
new XElement("Quantity", new XAttribute("UOMDescription", "VALUE"), new XAttribute("UOMCode", "VALUE"),
new XElement("Amount", "Value")),
new XElement("Quantity", new XAttribute("UOMDescription", "VALUE"), new XAttribute("UOMCode", "VALUE"),
new XElement("UnitPrice", "Value"))),
// End Loop
//Start Delivery Information
new XElement("OrderLine", new XAttribute("TypeDescription", "VALUE"), new XAttribute("Action", "VALUE"), new XAttribute("TypeCode", "VALUE"),
new XElement("LineNumber", new XAttribute("Preserve", "true"), "item_number + 1"),
new XElement("Product",
new XElement("SuppliersProductCode", "**** DELIVERY CODE ****"),
new XElement("Description", "**** DELIVERY DESCRIPTION****")),
new XElement("Quantity", new XAttribute("UOMDescription", "Piece"), new XAttribute("UOMCode", "PCE"),
new XElement("Amount", "1")),
new XElement("Quantity", new XAttribute("UOMDescription", "Pack"), new XAttribute("UOMCode", "PCK"),
new XElement("UnitPrice", "0"))),
new XElement("AdditionalOrderReferences",
new XElement("OrderReference", incrementid_var)),
new XElement("DeliveryInformation", "TELEPHONE NUMBER")));
Everything outside of the Start Loop, End Loop comments only needs to be generated once. If the order has 5 items I need to generate the code within those comments 5 times.
Any help is much appreciated as i'm going round in circles reading articles and still not sure how to implement this.
Thanks
Iain
