Output colonna XML

Question

Sto lavorando a un programma di console per generare un file XML utilizzando un database SQL Server e .NET.

Attualmente, sono bloccato con il mio codice per generare un file XML appropriato. Poiché il mio Categoriesnodo non è correttamente identificato come formato XML quando apro il file in Blocco note e, di conseguenza, non riesco a consumarlo bene.

Ciò che serve è questo:

1. Genera un tag XML appropriato per Categoriescome sotto e non dovrebbe includere caratteri speciali quando si apre in qualsiasi blocco note.

2. Se ho più ID sito (posizioni), come posso fare un ciclo for dalla mia tabella SQL contro il mio codice.

    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

Penso che sarà questo:

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

Ci sono ancora alcune opportunità qui per un refactoring significativo e ragionevole qui (la gestione di data / stringa / data deve essere eliminata e rifatta senza usare stringhe dove possibile), e potrebbe essere necessario correggere alcuni errori di battitura; non è un codice SQL facile da leggere e non mi aspetto che si incolli direttamente in SSMS e funzioni senza alcune modifiche!

Nota che GROUP BY STORE NO ma sembra che non lo usi

Answer 2

Prova 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);
            }
 
        }
    }
}