Other FORXMLEXPLICIT SQL function

1. Usage of FORXMLEXPLICIT

SQL Server Query 1

            
 SELECT  

     1 AS Tag,  

     Null AS Parent,  

     e.EmployeeId AS [Employee!1!EmployeeId],  

     e.LAStName AS [Employee!1!LAStName],  

     Null AS [EmployeeTerritories!2!TerritoryID]  

 FROM Employees e  

 UNION ALL  

 SELECT 2 AS Tag,  

     1 AS Parent,  

     et.EmployeeId AS [Employee!1!EmployeeId],  

     Null AS [Employee!1!LAStName],  

     et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]  

 FROM EmployeeTerritories et  

 ORDER BY [Employee!1!EmployeeId], Parent  

 FOR XML EXPLICIT;

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("1", "Tag")  

     .Column("Null", "Parent")  

     .Column("e.EmployeeId", "[Employee!1!EmployeeId]")  

     .Column("e.LastName", "[Employee!1!LastName]")  

     .Column("Null", "[EmployeeTerritories!2!TerritoryID]")  

     .From("Employees", "e")  

     .UnionAll(new SqlQueryBuilder()  

         .Select()  

         .Column("2", "Tag")  

         .Column("1", "Parent")  

         .Column("et.EmployeeId", "[Employee!1!EmployeeId]")  

         .Column("Null", "[Employee!1!LastName]")  

         .Column("et.TerritoryId", "[EmployeeTerritories!2!TerritoryID]")  

         .From("EmployeeTerritories", "et")  

         .OrderBy(new OrderBy().SetColumnAscending("[Employee!1!EmployeeId]")  

             .SetColumnAscending("Parent"))  

     )  

     .ForXml(new FOR_XML_EXPLICIT())  

     .Build();

Query build by SqlQueryBuilder 1

            
SELECT 1 AS Tag,
       NULL AS Parent,
       e.EmployeeId AS [Employee!1!EmployeeId],
       e.LastName AS [Employee!1!LastName],
       NULL AS [EmployeeTerritories!2!TerritoryID]
FROM Employees AS e
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       et.EmployeeId AS [Employee!1!EmployeeId],
       NULL AS [Employee!1!LastName],
       et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]
FROM EmployeeTerritories AS et
ORDER BY [Employee!1!EmployeeId] ASC, Parent ASC
FOR XML EXPLICIT;

Parameters (If used)

Name Value

Query Results 1:

  Col1
1 <Employee EmployeeId="1" LastName="Davolio"><EmployeeTerritories TerritoryID="06897"/><EmployeeTerritories TerritoryID="19713"/></Employee><Employee EmployeeId="2" LastName="Fuller"><EmployeeTerritories TerritoryID="01581"/><EmployeeTerritories TerritoryID="01730"/><EmployeeTerritories TerritoryID="01833"/><EmployeeTerritories TerritoryID="02116"/><EmployeeTerritories TerritoryID="02139"/><EmployeeTerritories TerritoryID="02184"/><EmployeeTerritories TerritoryID="40222"/></Employee><Employee EmployeeId="3" LastName="Leverling"><EmployeeTerritories TerritoryID="30346"/><EmployeeTerritories TerritoryID="31406"/><EmployeeTerritories TerritoryID="32859"/><EmployeeTerritories TerritoryID="33607"/></Employee><Employee EmployeeId="4" LastName="Peacock"><EmployeeTerritories TerritoryID="20852"/><EmployeeTerritories TerritoryID="27403"/><EmployeeTerritories TerritoryID="27511"/></Employee><Employee EmployeeId="5" LastName="Buchanan"><EmployeeTerritories TerritoryID="02903"/><EmployeeTerritories TerritoryID="07960"/><EmployeeTerritories TerritoryID="08837"/><EmployeeTerritories TerritoryID="10019"/><EmployeeTerritories TerritoryID="10038"/><EmployeeTerritories TerritoryID="11747"/><EmployeeTerritories TerritoryID="14450"/></Employee><Employee EmployeeId="6" LastName="Suyama"><EmployeeTerritories TerritoryID="85014"/><EmployeeTerritories TerritoryID="85251"/><EmployeeTerritories TerritoryID="98004"/><EmployeeTerritories TerritoryID="98052"/><EmployeeTerritories TerritoryID="98104"/></Employee><Employee EmployeeId="7" LastName="King"><EmployeeTerritories TerritoryID="60179"/><EmployeeTerritories TerritoryID="60601"/><EmployeeTerritories TerritoryID="80202"/><EmployeeTerritories TerritoryID="80909"/><EmployeeTerritories TerritoryID="90405"/><EmployeeTerritories TerritoryID="94025"/><EmployeeTerritories TerritoryID="94105"/><EmployeeTerritories TerritoryID="95008"/><EmployeeTerritories TerritoryID="95054"/><EmployeeTerritories TerritoryID="95060"/></Employee><Employee EmployeeId="8" LastName="Callahan"><Employee

2. Usage of FORXMLRAW

SQL Server Query 2

            
 SELECT  

     1 AS Tag,  

     Null AS Parent,  

     e.EmployeeId AS [Employee!1!EmployeeId],  

     e.LAStName AS [Employee!1!LAStName],  

     Null AS [EmployeeTerritories!2!TerritoryID]  

 FROM Employees e  

 UNION ALL  

 SELECT 2 AS Tag,  

     1 AS Parent,  

     et.EmployeeId AS [Employee!1!EmployeeId],  

     Null AS [Employee!1!LAStName],  

     et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]  

 FROM EmployeeTerritories et  

 ORDER BY [Employee!1!EmployeeId], Parent  

 FOR XML EXPLICIT, ROOT;

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("1", "Tag")  

     .Column("Null", "Parent")  

     .Column("e.EmployeeId", "[Employee!1!EmployeeId]")  

     .Column("e.LastName", "[Employee!1!LastName]")  

     .Column("Null", "[EmployeeTerritories!2!TerritoryID]")  

     .From("Employees", "e")  

     .UnionAll(new SqlQueryBuilder()  

         .Select()  

         .Column("2", "Tag")  

         .Column("1", "Parent")  

         .Column("et.EmployeeId", "[Employee!1!EmployeeId]")  

         .Column("Null", "[Employee!1!LastName]")  

         .Column("et.TerritoryId", "[EmployeeTerritories!2!TerritoryID]")  

         .From("EmployeeTerritories", "et")  

         .OrderBy(new OrderBy().Set(new Column("[Employee!1!EmployeeId]"))  

             .Set(new Column("Parent")))  

     )  

     .ForXml(new FOR_XML_EXPLICIT().ROOT())  

     .Build();

Query build by SqlQueryBuilder 2

            
SELECT 1 AS Tag,
       NULL AS Parent,
       e.EmployeeId AS [Employee!1!EmployeeId],
       e.LastName AS [Employee!1!LastName],
       NULL AS [EmployeeTerritories!2!TerritoryID]
FROM Employees AS e
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       et.EmployeeId AS [Employee!1!EmployeeId],
       NULL AS [Employee!1!LastName],
       et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]
FROM EmployeeTerritories AS et
ORDER BY [Employee!1!EmployeeId] ASC, Parent ASC
FOR XML EXPLICIT, ROOT;

Parameters (If used)

Name Value

Query Results 2:

  Col1
1 <Employee EmployeeId="1" LastName="Davolio"><EmployeeTerritories TerritoryID="06897"/><EmployeeTerritories TerritoryID="19713"/></Employee><Employee EmployeeId="2" LastName="Fuller"><EmployeeTerritories TerritoryID="01581"/><EmployeeTerritories TerritoryID="01730"/><EmployeeTerritories TerritoryID="01833"/><EmployeeTerritories TerritoryID="02116"/><EmployeeTerritories TerritoryID="02139"/><EmployeeTerritories TerritoryID="02184"/><EmployeeTerritories TerritoryID="40222"/></Employee><Employee EmployeeId="3" LastName="Leverling"><EmployeeTerritories TerritoryID="30346"/><EmployeeTerritories TerritoryID="31406"/><EmployeeTerritories TerritoryID="32859"/><EmployeeTerritories TerritoryID="33607"/></Employee><Employee EmployeeId="4" LastName="Peacock"><EmployeeTerritories TerritoryID="20852"/><EmployeeTerritories TerritoryID="27403"/><EmployeeTerritories TerritoryID="27511"/></Employee><Employee EmployeeId="5" LastName="Buchanan"><EmployeeTerritories TerritoryID="02903"/><EmployeeTerritories TerritoryID="07960"/><EmployeeTerritories TerritoryID="08837"/><EmployeeTerritories TerritoryID="10019"/><EmployeeTerritories TerritoryID="10038"/><EmployeeTerritories TerritoryID="11747"/><EmployeeTerritories TerritoryID="14450"/></Employee><Employee EmployeeId="6" LastName="Suyama"><EmployeeTerritories TerritoryID="85014"/><EmployeeTerritories TerritoryID="85251"/><EmployeeTerritories TerritoryID="98004"/><EmployeeTerritories TerritoryID="98052"/><EmployeeTerritories TerritoryID="98104"/></Employee><Employee EmployeeId="7" LastName="King"><EmployeeTerritories TerritoryID="60179"/><EmployeeTerritories TerritoryID="60601"/><EmployeeTerritories TerritoryID="80202"/><EmployeeTerritories TerritoryID="80909"/><EmployeeTerritories TerritoryID="90405"/><EmployeeTerritories TerritoryID="94025"/><EmployeeTerritories TerritoryID="94105"/><EmployeeTerritories TerritoryID="95008"/><EmployeeTerritories TerritoryID="95054"/><EmployeeTerritories TerritoryID="95060"/></Employee><Employee EmployeeId="8" LastName="Callahan"><Employee

3. Usage of FORXMLEXPLICIT

SQL Server Query 3

            
 SELECT  

     1 AS Tag,  

     Null AS Parent,  

     e.EmployeeId AS [Employee!1!EmployeeId],  

     e.LAStName AS [Employee!1!LAStName],  

     Null AS [EmployeeTerritories!2!TerritoryID]  

 FROM Employees e  

 UNION ALL  

 SELECT 2 AS Tag,  

     1 AS Parent,  

     et.EmployeeId AS [Employee!1!EmployeeId],  

     Null AS [Employee!1!LAStName],  

     et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]  

 FROM EmployeeTerritories et  

 ORDER BY [Employee!1!EmployeeId], Parent  

 FOR XML EXPLICIT, ROOT('employees');

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("1", "Tag")  

     .Column("Null", "Parent")  

     .Column("e.EmployeeId", "[Employee!1!EmployeeId]")  

     .Column("e.LastName", "[Employee!1!LastName]")  

     .Column("Null", "[EmployeeTerritories!2!TerritoryID]")  

     .From("Employees", "e")  

     .UnionAll(new SqlQueryBuilder()  

         .Select()  

         .Column("2", "Tag")  

         .Column("1", "Parent")  

         .Column("et.EmployeeId", "[Employee!1!EmployeeId]")  

         .Column("Null", "[Employee!1!LastName]")  

         .Column("et.TerritoryId", "[EmployeeTerritories!2!TerritoryID]")  

         .From("EmployeeTerritories", "et")  

         .OrderBy(new OrderBy().Set(new Column("[Employee!1!EmployeeId]"))  

             .Set(new Column("Parent")))  

     )  

     .ForXml(new FOR_XML_EXPLICIT().ROOT("employees"))  

     .Build();

Query build by SqlQueryBuilder 3

            
SELECT 1 AS Tag,
       NULL AS Parent,
       e.EmployeeId AS [Employee!1!EmployeeId],
       e.LastName AS [Employee!1!LastName],
       NULL AS [EmployeeTerritories!2!TerritoryID]
FROM Employees AS e
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       et.EmployeeId AS [Employee!1!EmployeeId],
       NULL AS [Employee!1!LastName],
       et.TerritoryId AS [EmployeeTerritories!2!TerritoryID]
FROM EmployeeTerritories AS et
ORDER BY [Employee!1!EmployeeId] ASC, Parent ASC
FOR XML EXPLICIT, ROOT ('employees');

Parameters (If used)

Name Value

Query Results 3:

  Col1
1 <Employee EmployeeId="1" LastName="Davolio"><EmployeeTerritories TerritoryID="06897"/><EmployeeTerritories TerritoryID="19713"/></Employee><Employee EmployeeId="2" LastName="Fuller"><EmployeeTerritories TerritoryID="01581"/><EmployeeTerritories TerritoryID="01730"/><EmployeeTerritories TerritoryID="01833"/><EmployeeTerritories TerritoryID="02116"/><EmployeeTerritories TerritoryID="02139"/><EmployeeTerritories TerritoryID="02184"/><EmployeeTerritories TerritoryID="40222"/></Employee><Employee EmployeeId="3" LastName="Leverling"><EmployeeTerritories TerritoryID="30346"/><EmployeeTerritories TerritoryID="31406"/><EmployeeTerritories TerritoryID="32859"/><EmployeeTerritories TerritoryID="33607"/></Employee><Employee EmployeeId="4" LastName="Peacock"><EmployeeTerritories TerritoryID="20852"/><EmployeeTerritories TerritoryID="27403"/><EmployeeTerritories TerritoryID="27511"/></Employee><Employee EmployeeId="5" LastName="Buchanan"><EmployeeTerritories TerritoryID="02903"/><EmployeeTerritories TerritoryID="07960"/><EmployeeTerritories TerritoryID="08837"/><EmployeeTerritories TerritoryID="10019"/><EmployeeTerritories TerritoryID="10038"/><EmployeeTerritories TerritoryID="11747"/><EmployeeTerritories TerritoryID="14450"/></Employee><Employee EmployeeId="6" LastName="Suyama"><EmployeeTerritories TerritoryID="85014"/><EmployeeTerritories TerritoryID="85251"/><EmployeeTerritories TerritoryID="98004"/><EmployeeTerritories TerritoryID="98052"/><EmployeeTerritories TerritoryID="98104"/></Employee><Employee EmployeeId="7" LastName="King"><EmployeeTerritories TerritoryID="60179"/><EmployeeTerritories TerritoryID="60601"/><EmployeeTerritories TerritoryID="80202"/><EmployeeTerritories TerritoryID="80909"/><EmployeeTerritories TerritoryID="90405"/><EmployeeTerritories TerritoryID="94025"/><EmployeeTerritories TerritoryID="94105"/><EmployeeTerritories TerritoryID="95008"/><EmployeeTerritories TerritoryID="95054"/><EmployeeTerritories TerritoryID="95060"/></Employee><Employee EmployeeId="8" LastName="Callahan"><Employee