代码之家 › 专栏 › 技术社区 › CJM

从SQL Server 2008填充父对象和子对象(通过XML?)

xml-deserialization xml-serialization xml sql-server c#

CJM · 技术社区 · 14 年前

我有一个简单的DAL,由 SalesEnquiry 对象,其中包括 List<T> 的 Vehicle

我不想返回传统的记录集并遍历它们,手动填充salesInquiry/Vehicle对象的每个属性,我想我可以从sqlserver返回XML格式的数据并反序列化它。我已经在第一时间使用这种技术来处理传入的数据。

但是,我不确定是否/如何在SQLServer中构造适当的XML,或者是否必须分两个阶段来完成。

以下内容将把查询提取为XML:

Select EnquiryID as 'enquiry/enquiryid',
     EnquiryNo as 'enquiry/enquiryno',
     CompanyName as 'enquiry/company'
From Enquiries e
Where e.EnquiryID = 23
For XML PATH

下面将把相关车辆提取为XML:

Select VehicleID as 'vehicle/vehicleid',
    VehicleReg as 'vehicle/vehiclereg'
From Vehicles v
Where v.EnquiryID= 23
For XML PATH

<?xml version="1.0" encoding="utf-8"?>
<enquiry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <enquiry_id>123</enquiry_no>
  <enquiry_no>100004</enquiry_no>
  <company>MyCompany</company>
  <enquiry_no>100004</enquiry_no>
  <vehicles>
    <vehicle>
      <vehicle_registration>ABC123</vehicle_registration>
    </vehicle>
    <vehicle>
      <vehicle_registration>XYZ789</vehicle_registration>
    </vehicle>
  </vehicles>
</enquiry>

我可以在SQL Server中创建它,还是必须在DAL中手动创建?

更新:

根据顺蒂的建议,我使用以下方法:

Select enquiry.EnquiryID as enquiry_id, enquiry.EnquiryNo, enquiry.CompanyName, VehicleID as [vehicle.vehicle_id], VehicleReg as [vehicle.vehicle_registration]
From Enquiries as enquiry
inner join Vehicles on Vehicles.EnquiryID = enquiry.EnquiryID
Where enquiry.EnquiryID = 23
For XML AUTO, ELEMENTS

<?xml version="1.0" encoding="utf-8"?>
<enquiry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <enquiry_id>123</enquiry_no>
  <enquiry_no>100004</enquiry_no>
  <company>MyCompany</company>
  <enquiry_no>100004</enquiry_no>
    <vehicle>
      <vehicle_registration>ABC123</vehicle_registration>
    </vehicle>
    <vehicle>
      <vehicle_registration>XYZ789</vehicle_registration>
    </vehicle>
</enquiry>

要成功反序列化,我需要 <vehicle> 要归入 <vehicles>

2 回复 | 直到 14 年前

8kb 14 年前

SELECT
  EnquiryID AS enquiry_id,
  EnquiryNo AS enquiry_no,
  CompanyName AS company,
  (
    SELECT
      VehicleReg AS vehicle_registration
    FROM Vehicles
    WHERE EnquiryID = e.EnquiryID
    FOR XML PATH ('vehicle'), TYPE, ROOT('vehicles')
   )
FROM Enquiries e 
WHERE EnquiryID = 123
FOR XML PATH ('enquiry'), TYPE

退货:

<enquiry>
  <enquiry_id>123</enquiry_id>
  <enquiry_no>100004</enquiry_no>
  <company>MyCompany</company>
  <vehicles>
    <vehicle>
      <vehicle_registration>ABC123</vehicle_registration>
    </vehicle>
    <vehicle>
      <vehicle_registration>XYZ789</vehicle_registration>
    </vehicle>
  </vehicles>
</enquiry>

shunty 14 年前

使用标准的内部连接,然后查看 AUTO