Pulling XML data from SQL Server to be parsed into JSON formatting problems in C#

[Alonex]

I'm using SQLCommand in C# to run SQL against a local machine which will output XML. The SQL code looks like this:\[code\]"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')";\[/code\]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.I tried to do something similar to this:\[code\] ' ' 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,\[/code\]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:\[code\]{"Receipt":{ ......//omitted data ,"Customer":{"Name: {"title":"Mr."}}\[/code\]My intended output would have all of the data organized inside of {"Receipt": without the use of another {. Similar to this:\[code\] "Receipt" : { "InvcHdrNotes" : "" "Tax" : "" "TaxPrc" : "" "DiscPrc" : "" "Discount" : "" "InvcComment1" : "" "InvcComment2" : ""}\[/code\]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.My Goal XML output would be:\[code\]<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>\[/code\]I get this output with the SQL code above but the identical information is repeated after the which throws the exception I mentioned.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.