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 ouput 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?

@KiroYakuza No, Programmers.SE is about software development concepts, e.g. algorithms, design patterns, or questions regarding the software development life cycle. Please do not recommend another site without understanding its scope. — amon
– amon, Commented Dec 20, 2015 at 17:02
okay so @amon , i am asking this question at the right place? — C Python
– C Python, Commented Dec 20, 2015 at 17:10
@CPython To be honest, I'm not quite sure. What precisely is the problem you're trying to solve? If you're asking how you can modify your code to get the second output, that's something we can't answer without more details. If your code already works as expected but you want to improve the code style etc., you might be looking for a code review. There's a special site for that, but again I'm not sure if the question is well-suited for that site. I'll just summon them by saying their name 3 times, maybe someone will help us: Code Review! Code Review! Code Review arise! — amon
– amon, Commented Dec 20, 2015 at 17:18
@amon Once is enough. If the code works, it looks on-topic for Code Review on first sight. — Mast
– Mast, Commented Dec 20, 2015 at 17:20

Community · Accepted Answer · 2017-05-23 12:04:07Z

0

In sql server support lot of xml format outputs.
This query returns a xml document from two tables. Maybe you are using linq, then after completing your sql query, you execute the query from linq.

select table1.column1, table2.column2 from table1 inner join table2 on table2.table1_id = table1.Id for xml auto

Check this link.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Jan 19, 2016 at 8:06

Siva G

988 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

チーズパン · Accepted Answer · 2016-01-19 09:15:49Z

I dont know if this is better but I would probably make some variables instead of calling the linq in the xml. Then you can just call the variable in the xml document.

Something like this maybe:

 private static void CreateDatabaseXml(string path)
{
    tbchrDataContext db = new tbchrDataContext();

    var rARefNum = db.T_ORDER_DETAILs.Select(i => i.RARefNum).Single();
    var customer = db.T_ORDER_HEADERs.Select(i => i.Customer).Single();
    var supplierName = db.T_ORDER_DETAILs.Select(i => i.SupplierName).Single();

    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", new XElement(ns + "RARefNum", rARefNum), 
                                    new XElement (ns + "WMSCategory", customer),
                                    new XElement (ns + "CustomerID", supplierName)))) );

    #endregion
    doc.Save(path);
}

Collectives™ on Stack Overflow

Using LINQ to output SQL data from two tables into XML

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related