Đầu ra cột XML

Question

Tôi đang làm việc trên một chương trình bảng điều khiển để tạo tệp XML bằng cơ sở dữ liệu SQL Server và .NET.

Hiện tại, tôi đang gặp khó khăn với mã của mình để tạo tệp XML thích hợp. Vì Categoriesnút của tôi không được xác định đúng định dạng XML khi tôi mở tệp trong Notepad và do đó, tôi không thể sử dụng nó tốt.

Điều cần thiết là:

1. Tạo thẻ XML thích hợp cho Categoriesnhư bên dưới & không nên bao gồm bất kỳ ký tự đặc biệt nào khi mở trong bất kỳ sổ tay nào.

2. Nếu tôi có nhiều ID trang web (vị trí), làm cách nào tôi có thể thực hiện một vòng lặp for từ bảng SQL với mã của tôi.

    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

Tôi nghĩ nó sẽ là thế này:

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')

Ở đây vẫn còn một số cơ hội để tái cấu trúc đáng kể, hợp lý ở đây (việc xử lý ngày / chuỗi / ngày cần vứt bỏ và thực hiện lại mà không sử dụng chuỗi nếu có thể) và có thể cần sửa lỗi chính tả; nó không phải là một SQL dễ đọc và tôi không mong đợi nó sẽ chỉ dán thẳng vào SSMS và hoạt động mà không cần một số chỉnh sửa!

Lưu ý rằng bạn NHÓM THEO CỬA HÀNG KHÔNG nhưng dường như bạn không sử dụng nó

Answer 2

Hãy thử 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);
            }
 
        }
    }
}