使用LINQ将两个表中的SQL数据输出为XML
本文关键字：SQL 数据 输出 XML 两个 LINQ 使用 | 更新日期: 2023-09-27 18:27:25
我对此非常陌生。希望有人能帮我建议如何改进代码。
我有两个表，需要在其中获取SQL数据并将其输出为XML格式。我正在使用LINQ方法。下面是代码的样子。
#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);
}
下面是XML的输出。
<?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>
最终，我想实现以下XML，从SQL获取的一些数据来自一个XML嵌套/元素中的两个独立表。
<?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>
有更好的编码方法吗？
- sql server支持大量xml格式输出
-
此查询从两个表中返回一个xml文档。也许你正在使用linq，然后在完成sql查询后，从林克。
从table1中选择table1.column1，table2.column2内部联接table2.table1_id=table1.id进行xml自动
检查此链接。
我不知道这是否更好，但我可能会生成一些变量，而不是在xml中调用linq。然后您可以直接调用xml文档中的变量。
可能是这样的：
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);
}