Other FORXMLAUTO SQL function

1. Usage of FORMATXMLAUTO

SQL Server Query 1

            
 SELECT TOP 10  

     c.CustomerID AS "@CustomerID", -- Prefixing with '@' makes it an attribute  

     c.CompanyName AS "@CompanyName",  

     o.OrderID AS "Order/@OrderID",  

     o.OrderDate AS "Order/@OrderDate"  

 FROM  

     Customers c  

 LEFT JOIN  

     Orders o ON c.CustomerID = o.CustomerID  

 WHERE c.Country = 'USA'  

 ORDER BY c.CustomerID, o.OrderID  

 FOR XML AUTO;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .ColumnXml("c.CustomerID", "@CustomerID")  

     .ColumnXml("c.CompanyName", "@CompanyName")  

     .ColumnXml("o.OrderID", "Order/@OrderID")  

     .ColumnXml("o.OrderDate", "Order/@OrderDate")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Orders","o"))  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .Where(new Where(new Column("c.Country").Equale("USA")))  

     .OrderBy(new OrderBy().Set(new Column("c.CustomerID"))  

         .Set(new Column("o.OrderID")))  

     .ForXml(new FOR_XML_AUTO().ELEMENTS())  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 c.CustomerID AS '@CustomerID',
              c.CompanyName AS '@CompanyName',
              o.OrderID AS 'Order/@OrderID',
              o.OrderDate AS 'Order/@OrderDate'
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
WHERE c.Country = @pMAIN_2507200132162477400
ORDER BY c.CustomerID ASC, o.OrderID ASC
FOR XML AUTO, ELEMENTS;


            
        

Parameters (If used)

Query Results 1:

2. Usage of FORXMLAUTO

SQL Server Query 2

            
 SELECT TOP 10  

     c.CustomerID AS "@CustomerID", -- Prefixing with '@' makes it an attribute  

     c.CompanyName AS "@CompanyName",  

     o.OrderID AS "Order/@OrderID",  

     o.OrderDate AS "Order/@OrderDate"  

 FROM  

     Customers c  

 LEFT JOIN  

     Orders o ON c.CustomerID = o.CustomerID  

 WHERE c.Country = 'USA'  

 ORDER BY c.CustomerID, o.OrderID  

 FOR XML AUTO, ELEMENTS;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .ColumnXml("c.CustomerID", "@CustomerID")  

     .ColumnXml("c.CompanyName", "@CompanyName")  

     .ColumnXml("o.OrderID", "Order/@OrderID")  

     .ColumnXml("o.OrderDate", "Order/@OrderDate")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new LEFTJOIN().TableName(new Table("Orders","o"))  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .Where(new Where(new Column("c.Country").Equale("USA")))  

     .OrderBy(new OrderBy().Set(new Column("c.CustomerID"))  

         .Set(new Column("o.OrderID")))  

     .ForXml(new FOR_XML_AUTO())  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 10 c.CustomerID AS '@CustomerID',
              c.CompanyName AS '@CompanyName',
              o.OrderID AS 'Order/@OrderID',
              o.OrderDate AS 'Order/@OrderDate'
FROM Customers AS c
     LEFT OUTER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
WHERE c.Country = @pMAIN_2507200132162589510
ORDER BY c.CustomerID ASC, o.OrderID ASC
FOR XML AUTO;


            
        

Parameters (If used)

Query Results 2: