Using LINQ to output SQL data from two tables into XML

Question

I am very new to this. Hope someone could help me suggest how to improve the code.

I have two tables where I need to get the SQL data and output it into XML format. I am using LINQ method. Below how the code looks like.

 #region Database XML Methods
private static void CreateDatabaseXml(string path)
{
 tbchrDataContext db = new tbchrDataContext();
 XDocument doc = new XDocument(
 // XML Declaration
 new XDeclaration("1.0", "utf-8", "yes"),
 // XML Root element to 3rd in nest
 new XElement(ns + "WMS",
 new XElement(ns + "Order",
 new XElement(ns + "Header", from a in db.T_ORDER_DETAILs
 select new XElement(ns + "RARefNum", a.RARefNum), 
 new XElement (ns + "WMSCategory", from b in db.T_ORDER_HEADERs select b.Customer),
 new XElement (ns + "CustomerID", from a in db.T_ORDER_DETAILs select a.SupplierName)))) );
 #endregion
 doc.Save(path);
}

And below how is the output of XML looks like.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<WMS xmlns="http://blog.cripperz.sg">
 <Order>
 <Header>
 <RARefNum>RASO000001</RARefNum>
 <RARefNum>RASO000001</RARefNum>
 <WMSCategory>ESSVMI</WMSCategory>
 <CustomerID>nVidianVidia</CustomerID>
 </Header>
 </Order>
</WMS>

Ultimately I wanted to achieve the below XML, some of the data grab from SQL is from two separate tables in one XML nest / element.

<?xml version="1.0" encoding="utf-8"?>
<WMS>
 <Order>
 <Header>
 <RARefNum>RASO000001</RARefNum>
 <WMSCategory>ESSVMI</WMSCategory>
 <CustomerID>nVidia</CustomerID>
 <CreationDate>2013年12月02日 06:29:50</CreationDate>
 <OrderDate>2013年12月02日 06:29:50</OrderDate>
 <ExpectedShippedDate>2013年12月02日 06:29:50</ExpectedShippedDate>
 <LastShippedDate>2013年12月02日 06:29:50</LastShippedDate>
 <CustomerOrderReference>nVidia9338</CustomerOrderReference>
 <CustomerShipmentNo>81475721</CustomerShipmentNo>
 <CustomerSONo>SO982733</CustomerSONo>
 <CustomerInvoiceNo>INV987373</CustomerInvoiceNo>
 <CustomerReference1>nVidia 1</CustomerReference1>
 <CustomerReference2/>
 <WMSReference1>Emp 1</WMSReference1>
 <WMSReference2>Emp 2</WMSReference2>
 <ShipmentNo>IWU997872</ShipmentNo>
 <DocumentNo>KK98764394</DocumentNo>
 <Transportation>
 <Mode>Freight</Mode>
 <VehicleType/>
 </Transportation>
 <Carrier>
 <ID>Fedex</ID>
 <Name>Fedex SG</Name>
 <Address>Changi Singapore</Address>
 <Country/>
 <PostalCode/>
 <Contact>
 <Sequence/>
 <Person/>
 <Email/>
 <DID/>
 <Handphone/>
 </Contact>
 </Carrier>
 <Consignee>
 <ID>ABC</ID>
 <Name>ABC Corp</Name>
 <Address>Jurong West, Singapore</Address>
 <Country/>
 <PostalCode/>
 <Contact>
 <Sequence/>
 <Person/>
 <Email/>
 <DID/>
 <Handphone/>
 </Contact>
 </Consignee>
 <Containers/>
 </Header>
 <Details>
 <Detail>
 <LineNo>1</LineNo>
 <SKU>SKU0001</SKU>
 <SKUDescription>SKU 0001</SKUDescription>
 <Package>50</Package>
 <OrderedQty>600.000</OrderedQty>
 <PickedQty>600.000</PickedQty>
 <PickedDate>2013年12月02日 06:35:09</PickedDate>
 <ShippedQty>600.000</ShippedQty>
 <ShippedDate>2013年12月02日 06:35:09</ShippedDate>
 <ManufactoryDate>2013年12月02日 06:35:09</ManufactoryDate>
 <ExpiryDate>2014年12月02日 06:35:09</ExpiryDate>
 <FIFODate>2013年06月02日 06:35:09</FIFODate>
 <CustomerLotRef1>nVidia 2093</CustomerLotRef1>
 <CustomerLotRef2>nVidia 2099</CustomerLotRef2>
 <LineReference1>10</LineReference1>
 </Detail>
 <Detail>
 <LineNo>2</LineNo>
 <SKU>SKU0002</SKU>
 <SKUDescription>SKU 0002</SKUDescription>
 <Package>50</Package>
 <OrderedQty>100.000</OrderedQty>
 <PickedQty>100.000</PickedQty>
 <PickedDate>2013年12月02日 06:35:09</PickedDate>
 <ShippedQty>100.000</ShippedQty>
 <ShippedDate>2013年12月02日 06:35:09</ShippedDate>
 <ManufactoryDate>2013年12月02日 06:35:09</ManufactoryDate>
 <ExpiryDate>2014年12月02日 06:35:09</ExpiryDate>
 <FIFODate>2013年06月02日 06:35:09</FIFODate>
 <CustomerLotRef1>nVidia 2193</CustomerLotRef1>
 <CustomerLotRef2>nVidia 2199</CustomerLotRef2>
 <LineReference1>10</LineReference1>
 </Detail>
 </Details>
 </Order>
</WMS>

Is there a better way to code it?

Answer 1

My suggestion is for this use XML serialization. Check the Sample

Simply what you have to do is arrange a class structure according to your XML required and then create your object and use serialization to create your XML.

If you need more help let me know I can help you.