Keluaran Kolom XML

Question

Saya sedang mengerjakan program konsol untuk menghasilkan file XML menggunakan database SQL Server dan .NET.

Saat ini, saya terjebak dengan kode saya untuk menghasilkan file XML yang tepat. Karena Categoriesnode saya tidak diidentifikasi dengan benar sebagai format XML ketika saya membuka file di Notepad dan akibatnya, saya tidak dapat menggunakannya dengan baik.

Yang dibutuhkan adalah ini:

1. Hasilkan tag XML yang tepat untuk Categoriesseperti di bawah ini & tidak boleh menyertakan karakter khusus saat membuka di notepad mana pun.

2. Jika saya memiliki beberapa ID situs (lokasi), bagaimana saya dapat melakukan loop for dari tabel SQL saya terhadap kode saya.

    Dim dsXmlDoc As XmlDocument = New XmlDocument()
    ds.Tables(0).TableName = "Inv"
    dsXmlDoc.LoadXml(ds.GetXml())
   
    Dim xmlInner As XmlElement = CType(xmlDoc.SelectSingleNode("/Root/"),
    XmlElement)xmlInner.AppendChild(xmlDoc.ImportNode(dsXmlDoc.DocumentElement, True))
   

Answer 1

Saya pikir itu akan menjadi ini:

WITH x AS (
  SELECT 
    A.[ORDER ID],
    RIGHT(A.[POS ID], 2) AS [LesseePOSNumber],
    REPLACE(A.[BUSINESS DATE],'-','') + '-' + A.[RECEIPT NUMBER] AS [InvoiceNumber],
    '1' AS [DocumentType],
    CAST(A.[BUSINESS DATE] AS datetime) + CAST(A.[START SALE TIME] AS DATETIME) AS [TransactionDateTime],
    SUM(A.ITEM) AS [TotalNumberOfItems],
    SUM(A.[TOTAL AMOUNT] - A.[TOTAL TAX]) AS [TransactionTotalWithoutVAT],
    SUM(A.[TOTAL TAX]) AS [VATTotalAmount],
    '1' AS [PaymentType],
    '4' AS [Currency]
  FROM 
  (
    SELECT 
           T1.[storeId] AS [STORE NO]
          ,CONVERT(date,CONVERT(varchar(15),T1.businessDate,101)) AS [BUSINESS DATE]
          ,FORMAT(CONVERT(datetime,SUBSTRING(T1.startSaleTime,1,2) + ':' + SUBSTRING(T1.startSaleTime,3,2) + ':' + SUBSTRING(T1.startSaleTime,5,2), 121), 'h:mm:ss') AS [START SALE TIME]
          ,CASE WHEN (T1.Type) = 'TRX_Refund' OR (T1.Type) = 'TRX_Overring' THEN CAST(T1.[totalAmount] AS decimal(16,2)) * -1 ELSE CAST(T1.[totalAmount] AS decimal(16,2)) END AS [TOTAL AMOUNT]
          ,CASE WHEN (T1.Type) = 'TRX_Refund' OR (T1.Type) = 'TRX_Overring' THEN 0 ELSE 1 END AS [GC] 
          ,CASE WHEN (T1.Type) = 'TRX_Refund' OR (T1.Type) = 'TRX_Overring' THEN CAST(T1.[totalTax] AS decimal(16,2)) * -1 ELSE CAST(T1.[totalTax] AS decimal(16,2)) END AS [TOTAL TAX]     
          ,T1.NodeId AS [POS ID]
          ,T1.[receiptNumber] AS [RECEIPT NUMBER]
          ,T1.orderId AS [ORDER ID]
          ,
          (
            SELECT SUM(CASE WHEN CAST(T2.totalPrice AS float) > 0.00 THEN 1 ELSE 0 END) 
            FROM [McDonalds_STG].[dbo].[ITEMLEVEL] T2
            WHERE T2.orderId = T1.orderId
          ) AS [ITEM]

    FROM [McDonalds_STG].[dbo].[Orders] T1

  ) A

  GROUP BY
    A.[STORE NO],
    A.[BUSINESS DATE],
    A.[POS ID],
    A.[RECEIPT NUMBER],
    A.[START SALE TIME],
    A.[ORDER ID]
)
SELECT 
  x.[LesseePOSNumber],
  x.[InvoiceNumber],
  x.[DocumentType],
  x.[TransactionDateTime],
  x.[TotalNumberOfItems],
  x.[TransactionTotalWithoutVAT],
  x.[VATTotalAmount],
  x.[PaymentType],
  x.[Currency], 
  (
    SELECT 
      49 AS [ID],
      (CAST(T1.[totalAmount] AS decimal(16,2)) - CAST(T1.[totalTax] AS decimal(16,2)) ) AS [Total]
    FROM [Order] T2  
    WHERE T2.orderId = x.[ORDER ID]  
    FOR XML PATH('Category'), TYPE 
  ) As Categories
FROM x
FOR XML PATH ('Invoice'), root ('Invoices')

Masih ada beberapa peluang di sini untuk pemfaktoran ulang yang signifikan dan masuk akal di sini (penanganan tanggal / string / tanggal perlu dibuang dan dilakukan lagi tanpa menggunakan string jika memungkinkan), dan mungkin perlu beberapa perbaikan kesalahan ketik; Ini bukan SQL yang mudah dibaca dan saya tidak berharap itu hanya akan menempel langsung ke SSMS dan bekerja tanpa beberapa tweak!

Perhatikan bahwa Anda GROUP BY STORE NO tetapi Anda tampaknya tidak menggunakannya

Answer 2

Coba xml linq:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENAME);

            Dictionary<string, XElement> dict = doc.Descendants("Invoice")
                .GroupBy(x => (string)x.Element("LesseePOSNumber"), y => y)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());

            string lesseePOSNumber = "1";
            string id = "29";
            int total = 3000;
            if (dict.ContainsKey(lesseePOSNumber))
            {
                XElement categories = new XElement("Categories", new XElement("Category", new object[] {
                    new XElement("ID", id),
                    new XElement("Total", total)
                }));

                dict[lesseePOSNumber].Add(categories);
            }
 
        }
    }
}