从SQL Server中提取XML数据，并将其解析为c#中的JSON格式问题

I'm using SQLCommand in C# to run SQL against a local machine which will output XML. The SQL code looks like this:

我在c#中使用SQLCommand来运行SQL，而不是本地机器，它将输出XML。SQL代码如下所示:

"SELECT 
   ' '                          AS 'ItemsCount', 
   ' '                          AS 'Shipping', 
   ' '                          AS 'Fee', 
   ' '                          AS 'ShippingPrc', 
   ' '                          AS 'FeeType', 
   ' '                          AS 'FeeTaxPrc', 
 //many lines of SQL omitted
 FROM   im_item 
   JOIN ps_doc_lin 
     ON im_item.item_no = ps_doc_lin.item_no 
   JOIN  ps_doc_hdr 
     ON ps_doc_hdr.doc_id = ps_doc_lin.doc_id 
   JOIN ar_cust 
   ON ar_cust.cust_no = ps_doc_hdr.cust_no 
 WHERE  ps_doc_hdr.tkt_dt = (SELECT Max(tkt_dt) //I select Max(tkt_dt) here because this program will run right after a customer has completed checkout.  I want to get the most recent ticket (i.e. the ticket just rang up)
                        FROM   ps_doc_hdr) 
 FOR xml path ('Receipt')";

This has been working but I recently discovered a fatal flaw. If in the database a customer has purchased multiple items, the XML output from SQL Server will be an XML file with that customer's information repeated for each item purchased. If I parse the XML output in C# it can't be done, the output results in multiple root elements. I suspect that the JOIN of the various tables is causing this but I am having to pull a variety of data from SQL and I need these joins to get to what I need.

这一直在起作用，但我最近发现了一个致命的缺陷。如果客户在数据库中购买了多个项目，那么SQL Server的XML输出将是一个XML文件，每个项目都重复该客户的信息。如果我用c#解析XML输出，结果是多个根元素。我怀疑各种表的连接导致了这种情况，但是我必须从SQL中提取各种数据，我需要这些连接才能得到我需要的数据。

I tried to do something similar to this:

我试着做一些类似的事情:

   ' '                          AS 'Total', 
   ' '                          AS 'InvcHdrRcptStatus', 
   ' '                          AS 'InvcHdrRcptType', 
   ' '                          AS 'Cashier', 
   ' '                          AS 'DocDate', 
   ' '                          AS 'InvcNum', 
   (SELECT      ps_doc_lin.sls_rep           AS 'Clerk'
                    FROM PS_DOC_LIN
                    WHERE PS_DOC_LIN.DOC_ID = PS_DOC_HDR.DOC_ID
                    FOR XML PATH ('Item')) as Items, 
   (SELECT     ar_cust.cust_no              AS 'BillToCustNumber', 
   ' '                          AS 'BillToCustCompany', 
   ar_cust.fst_nam              AS 'BillToFName', 
   ar_cust.lst_nam              AS 'BillToLName', 
   ar_cust.salutation           AS 'Customer/Name/Title', 
   ar_cust.adrs_1               AS 'BillToAddr1', 
   ar_cust.adrs_2               AS 'BillToAddr2', 
   ' '                          AS 'BillToAddr3', 
   ar_cust.zip_cod              AS 'BillToZip', 
   ' '                          AS 'BillToInfo1', 
   ' '                          AS 'BillToInfo2', 
   ' '                          AS 'BillToPhone1', 
   ' '                          AS 'BillToPhone2', 
   ar_cust.phone_1              AS 'ShipToPhone1', 
   ar_cust.phone_2              AS 'ShipToPhone2' FROM AR_CUST WHERE AR_CUST.CUST_NO = PS_DOC_HDR.CUST_NO FOR XML PATH ('Customer'), TYPE) as Customers,

I nested the JOIN into another select. While this did work, it didn't format correctly when converted to JSON. The output looks like this:

我将连接嵌套到另一个select中。虽然这确实有效，但在转换为JSON时，它并没有正确地格式化。输出如下:

{"Receipt":{ ......//omitted data
 ,"Customer":{"Name: {"title":"Mr."}}

My intended output would have all of the data organized inside of {"Receipt": without the use of another {. Similar to this:

我的预期输出将在{“收据”中组织所有数据:不使用另一个{。类似于:

    "Receipt" : {
    "InvcHdrNotes" : ""
    "Tax" : ""
    "TaxPrc" : ""
    "DiscPrc" : ""
    "Discount" : ""
    "InvcComment1" : ""
    "InvcComment2" : ""
}

The "Receipt" is a subsection of a much larger JSON file. However, this flaw will be replicated (I assume so) for the remainder of the data I need to get since I will need JOIN for those as well. I have tried using both Jayrock and Newtonsoft.Json but both have this issue.

“收据”是一个更大的JSON文件的子部分。但是，这个缺陷将被复制(我假设如此)用于我需要获得的其余数据，因为我也需要为这些数据加入JOIN。我试过同时使用Jayrock和Newtonsoft。但两者都有这个问题。

My Goal XML output would be:

我的目标XML输出是:

<Receipt>
<InvcHdrNotes> </InvcHdrNotes>
<Tax>Y</Tax>
<Clerk>MGR</Clerk>
<BillToCustNumber>1000</BillToCustNumber>
<BillToCustCompany> </BillToCustCompany>
<BillToFName>Bill</BillToFName>
<BillToLName>Baker</BillToLName>
<Customer>
  <Name>
    <Title>Mr.</Title>
  </Name>
</Customer>
<BillToAddr1>1426 Millstream Parkway</BillToAddr1>
<BillToAddr3> </BillToAddr3>
<BillToZip>38120</BillToZip>
<BillToInfo1> </BillToInfo1>
<BillToInfo2> </BillToInfo2>
<ShipToCustNumber>1000</ShipToCustNumber>
<ShipToCustCompany> </ShipToCustCompany>
<ShipToFName>Bill</ShipToFName>
<ShipToLName>Baker</ShipToLName>
<ShipToTitle>Mr.</ShipToTitle>
<ShipToAddr1>1426 Millstream Parkway</ShipToAddr1>
<ShipToZip>38120</ShipToZip>
</Receipt>

I get this output with the SQL code above but the identical information is repeated after the which throws the exception I mentioned.

我使用上面的SQL代码获得了这个输出，但是相同的信息在抛出我提到的异常之后重复出现。

Is this even possible or is this a fatal design flaw? If it isn't possible, I am unsure of how to complete this task. Thank you.

这是可能的还是致命的设计缺陷?如果不可能，我不确定如何完成这项任务。谢谢你！

1 个解决方案

#1

It looks like you're serializing each row returned to the same XML - so you would end up with multiple root elements. You need to serialize the List itself.

看起来您正在序列化返回到相同XML的每一行——因此您将得到多个根元素。您需要序列化列表本身。

So you'd end up with:

你会得到:

<Receipts>
  <Receipt>
    ...
  </Receipt>
  <Receipt>
    ...
  </Receipt>
</Receipts>

#1