Other FORXMLRAW SQL function

1. Usage of FORXMLRAW

SQL Server Query 1

            
 SELECT TOP 10  

     o.OrderID AS OrderID,  

     c.CustomerID AS Customer_CustomerID,  

     c.CompanyName AS Customer_CompanyName,  

     e.EmployeeID AS Employee_EmployeeID,  

     e.FirstName AS Employee_FirstName,  

     e.LastName AS Employee_LastName,  

     o.OrderDate AS OrderDate,  

     o.RequiredDate AS RequiredDate,  

     o.ShippedDate AS ShippedDate,  

     s.CompanyName AS Shipper_CompanyName,  

     o.Freight AS Freight  

 FROM  

     Orders o  

 JOIN  

     Customers c ON o.CustomerID = c.CustomerID  

 JOIN  

     Employees e ON o.EmployeeID = e.EmployeeID  

 LEFT JOIN  

     Shippers s ON o.ShipVia = s.ShipperID  

 WHERE o.OrderDate >= '19980101'  

 ORDER BY o.OrderID  

 FOR XML RAW  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .Column("o.OrderID", "OrderID")  

     .Column("c.CustomerID", "Customer_CustomerID")  

     .Column("c.CompanyName", "Customer_CompanyName")  

     .Column("e.EmployeeID", "Employee_EmployeeID")  

     .Column("e.FirstName", "Employee_FirstName")  

     .Column("e.LastName", "Employee_LastName")  

     .Column("o.OrderDate", "OrderDate")  

     .Column("o.RequiredDate", "RequiredDate")  

     .Column("o.ShippedDate", "ShippedDate")  

     .Column("s.CompanyName", "Shipper_CompanyName")  

     .Column("o.Freight", "Freight")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Customers","c"))  

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

         new INNERJOIN().TableName(new Table("Employees","e"))  

             .On(new Column("o.EmployeeID").Equale(new Column("e.EmployeeID"))),  

         new LEFTJOIN().TableName(new Table("Shippers","s"))  

             .On(new Column("o.ShipVia").Equale(new Column("s.ShipperID")))  

     })  

     .Where(new Where(new Column("o.OrderDate").Equale("19980101")))  

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

     .ForXml(new FOR_XML_RAW())  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 o.OrderID AS OrderID,
              c.CustomerID AS Customer_CustomerID,
              c.CompanyName AS Customer_CompanyName,
              e.EmployeeID AS Employee_EmployeeID,
              e.FirstName AS Employee_FirstName,
              e.LastName AS Employee_LastName,
              o.OrderDate AS OrderDate,
              o.RequiredDate AS RequiredDate,
              o.ShippedDate AS ShippedDate,
              s.CompanyName AS Shipper_CompanyName,
              o.Freight AS Freight
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     Employees AS e
     ON o.EmployeeID = e.EmployeeID
     LEFT OUTER JOIN
     Shippers AS s
     ON o.ShipVia = s.ShipperID
WHERE o.OrderDate = @pMAIN_2507192014399320080
ORDER BY o.OrderID ASC
FOR XML RAW;


            
        

Parameters (If used)

Query Results 1:

2. Usage of FORXMLRAW

SQL Server Query 2

            
 SELECT TOP 10  

     o.OrderID AS OrderID,  

     c.CustomerID AS Customer_CustomerID,  

     c.CompanyName AS Customer_CompanyName,  

     e.EmployeeID AS Employee_EmployeeID,  

     e.FirstName AS Employee_FirstName,  

     e.LastName AS Employee_LastName,  

     o.OrderDate AS OrderDate,  

     o.RequiredDate AS RequiredDate,  

     o.ShippedDate AS ShippedDate,  

     s.CompanyName AS Shipper_CompanyName,  

     o.Freight AS Freight  

 FROM  

     Orders o  

 JOIN  

     Customers c ON o.CustomerID = c.CustomerID  

 JOIN  

     Employees e ON o.EmployeeID = e.EmployeeID  

 LEFT JOIN  

     Shippers s ON o.ShipVia = s.ShipperID  

 WHERE o.OrderDate >= '19980101'  

 ORDER BY o.OrderID  

 FOR XML RAW('Order')  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .Column("o.OrderID", "OrderID")  

     .Column("c.CustomerID", "Customer_CustomerID")  

     .Column("c.CompanyName", "Customer_CompanyName")  

     .Column("e.EmployeeID", "Employee_EmployeeID")  

     .Column("e.FirstName", "Employee_FirstName")  

     .Column("e.LastName", "Employee_LastName")  

     .Column("o.OrderDate", "OrderDate")  

     .Column("o.RequiredDate", "RequiredDate")  

     .Column("o.ShippedDate", "ShippedDate")  

     .Column("s.CompanyName", "Shipper_CompanyName")  

     .Column("o.Freight", "Freight")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Customers","c"))  

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

         new INNERJOIN().TableName(new Table("Employees","e"))  

             .On(new Column("o.EmployeeID").Equale(new Column("e.EmployeeID"))),  

         new Join(SQLJoin.LEFTJOIN).TableName(new Table("Shippers","s"))  

             .On(new Column("o.ShipVia").Equale(new Column("s.ShipperID")))  

     })  

     .Where(new Where(new Column("o.OrderDate").GreaterThanOrEqualeTo("19980101")))  

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

     .ForXml(new FOR_XML_RAW("Order"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 10 o.OrderID AS OrderID,
              c.CustomerID AS Customer_CustomerID,
              c.CompanyName AS Customer_CompanyName,
              e.EmployeeID AS Employee_EmployeeID,
              e.FirstName AS Employee_FirstName,
              e.LastName AS Employee_LastName,
              o.OrderDate AS OrderDate,
              o.RequiredDate AS RequiredDate,
              o.ShippedDate AS ShippedDate,
              s.CompanyName AS Shipper_CompanyName,
              o.Freight AS Freight
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     Employees AS e
     ON o.EmployeeID = e.EmployeeID
     LEFT OUTER JOIN
     Shippers AS s
     ON o.ShipVia = s.ShipperID
WHERE o.OrderDate >= @pMAIN_2507192014400007650
ORDER BY o.OrderID ASC
FOR XML RAW ('Order');


            
        

Parameters (If used)

Query Results 2: