Saída de coluna XML

Question

Estou trabalhando em um programa de console para gerar um arquivo XML usando um banco de dados SQL Server e .NET.

Atualmente, estou preso com meu código para gerar um arquivo XML adequado. Como meu Categoriesnó não é identificado corretamente como formato XML quando abro o arquivo no Bloco de notas e, como resultado, não consigo consumi-lo bem.

O que é necessário é o seguinte:

1. Gere tags XML adequadas para Categorieso seguinte e não deve incluir nenhum caractere especial ao abrir em qualquer bloco de notas.

2. Se eu tiver vários sites ID (locais), como posso fazer um loop for da minha tabela SQL em relação ao meu código.

    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

Acho que vai ser assim:

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

Ainda há algumas oportunidades aqui para refatoração significativa e sensata aqui (o tratamento de data / string / data precisa ser jogado fora e fazer novamente sem usar strings quando possível) e pode precisar de alguns erros de digitação; não é um SQL fácil de ler e não espero que ele apenas cole direto no SSMS e funcione sem alguns ajustes!

Observe que você GROUP BY STORE NO, mas não parece usá-lo

Answer 2

Experimente o 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);
            }
 
        }
    }
}