Other FORXMLPATH SQL function

1. Usage of FORXMLPATH

SQL Server Query 1

            
 SELECT   

 	TitleOfCourtesy,  

 	FirstName,  

 	LastName,  

 	Title,  

 	BirthDate  

 FROM Employees  

 FOR XML PATH;

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("TitleOfCourtesy", "FirstName", "LastName", "Title", "BirthDate")  

     .From("Employees")  

     .ForXml(new FOR_XML_PATH())  

     .Build();

Query build by SqlQueryBuilder 1

            
SELECT TitleOfCourtesy,
       FirstName,
       LastName,
       Title,
       BirthDate
FROM Employees
FOR XML PATH;

Parameters (If used)

Name Value

Query Results 1:

  Col1
1 <row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Nancy</FirstName><LastName>Davolio</LastName><Title>Sales Representative</Title><BirthDate>1948-12-08T00:00:00</BirthDate></row><row><TitleOfCourtesy>Dr.</TitleOfCourtesy><FirstName>Andrew</FirstName><LastName>Fuller</LastName><Title>Vice President, Sales</Title><BirthDate>1952-02-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Janet</FirstName><LastName>Leverling</LastName><Title>Sales Representative</Title><BirthDate>1963-08-30T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mrs.</TitleOfCourtesy><FirstName>Margaret</FirstName><LastName>Peacock</LastName><Title>Sales Representative</Title><BirthDate>1937-09-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Steven</FirstName><LastName>Buchanan</LastName><Title>Sales Manager</Title><BirthDate>1955-03-04T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Michael</FirstName><LastName>Suyama</LastName><Title>Sales Representative</Title><BirthDate>1963-07-02T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Robert</FirstName><LastName>King</LastName><Title>Sales Representative</Title><BirthDate>1960-05-29T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Laura</FirstName><LastName>Callahan</LastName><Title>Inside Sales Coordinator</Title><BirthDate>1958-01-09T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Anne</FirstName><LastName>Dodsworth</LastName><Title>Sales Representative</Title><BirthDate>1966-01-27T00:00:00</BirthDate></row>

2. Usage of FORXMLPATH

SQL Server Query 2

            
 SELECT   

 	TitleOfCourtesy,  

 	FirstName,  

 	LastName,  

 	Title,  

 	BirthDate  

 FROM Employees  

 FOR XML PATH('Person');

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("TitleOfCourtesy", "FirstName", "LastName", "Title", "BirthDate")  

     .From("Employees")  

     .ForXml(new FOR_XML_PATH("Person"))  

     .Build();

Query build by SqlQueryBuilder 2

            
SELECT TitleOfCourtesy,
       FirstName,
       LastName,
       Title,
       BirthDate
FROM Employees
FOR XML PATH ('Person');

Parameters (If used)

Name Value

Query Results 2:

  Col1
1 <row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Nancy</FirstName><LastName>Davolio</LastName><Title>Sales Representative</Title><BirthDate>1948-12-08T00:00:00</BirthDate></row><row><TitleOfCourtesy>Dr.</TitleOfCourtesy><FirstName>Andrew</FirstName><LastName>Fuller</LastName><Title>Vice President, Sales</Title><BirthDate>1952-02-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Janet</FirstName><LastName>Leverling</LastName><Title>Sales Representative</Title><BirthDate>1963-08-30T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mrs.</TitleOfCourtesy><FirstName>Margaret</FirstName><LastName>Peacock</LastName><Title>Sales Representative</Title><BirthDate>1937-09-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Steven</FirstName><LastName>Buchanan</LastName><Title>Sales Manager</Title><BirthDate>1955-03-04T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Michael</FirstName><LastName>Suyama</LastName><Title>Sales Representative</Title><BirthDate>1963-07-02T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Robert</FirstName><LastName>King</LastName><Title>Sales Representative</Title><BirthDate>1960-05-29T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Laura</FirstName><LastName>Callahan</LastName><Title>Inside Sales Coordinator</Title><BirthDate>1958-01-09T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Anne</FirstName><LastName>Dodsworth</LastName><Title>Sales Representative</Title><BirthDate>1966-01-27T00:00:00</BirthDate></row>

3. Usage of FORXMLPATH

SQL Server Query 3

            
 SELECT   

 	TitleOfCourtesy,  

 	FirstName,  

 	LastName,  

 	Title,  

 	BirthDate  

 FROM Employees  

 FOR XML PATH('Person'), ROOT;

Create SQL query with SqlQueryBuilder 3

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("TitleOfCourtesy", "FirstName", "LastName", "Title", "BirthDate")  

     .From("Employees")  

     .ForXml(new FOR_XML_PATH("Person").ROOT())  

     .Build();

Query build by SqlQueryBuilder 3

            
SELECT TitleOfCourtesy,
       FirstName,
       LastName,
       Title,
       BirthDate
FROM Employees
FOR XML PATH ('Person'), ROOT;

Parameters (If used)

Name Value

Query Results 3:

  Col1
1 <row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Nancy</FirstName><LastName>Davolio</LastName><Title>Sales Representative</Title><BirthDate>1948-12-08T00:00:00</BirthDate></row><row><TitleOfCourtesy>Dr.</TitleOfCourtesy><FirstName>Andrew</FirstName><LastName>Fuller</LastName><Title>Vice President, Sales</Title><BirthDate>1952-02-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Janet</FirstName><LastName>Leverling</LastName><Title>Sales Representative</Title><BirthDate>1963-08-30T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mrs.</TitleOfCourtesy><FirstName>Margaret</FirstName><LastName>Peacock</LastName><Title>Sales Representative</Title><BirthDate>1937-09-19T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Steven</FirstName><LastName>Buchanan</LastName><Title>Sales Manager</Title><BirthDate>1955-03-04T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Michael</FirstName><LastName>Suyama</LastName><Title>Sales Representative</Title><BirthDate>1963-07-02T00:00:00</BirthDate></row><row><TitleOfCourtesy>Mr.</TitleOfCourtesy><FirstName>Robert</FirstName><LastName>King</LastName><Title>Sales Representative</Title><BirthDate>1960-05-29T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Laura</FirstName><LastName>Callahan</LastName><Title>Inside Sales Coordinator</Title><BirthDate>1958-01-09T00:00:00</BirthDate></row><row><TitleOfCourtesy>Ms.</TitleOfCourtesy><FirstName>Anne</FirstName><LastName>Dodsworth</LastName><Title>Sales Representative</Title><BirthDate>1966-01-27T00:00:00</BirthDate></row>