SQL Server Forxmlpath Simple application

Forxmlpath Simple ApplicationOne: Forxmlpath introduction

Forxmlpath is part of the forxml syntax in SQL, this article focuses on path mode

---Four pattern usages of the FOR XML clause in SQL

1. Auto mode: Returns the data table as the element of the table name, the value of each column is returned as a property;

SELECT * from Tb_test FOR XML auto

2. Raw mode: Returns the data behavior element, the value of each column as the attribute of the element;

SELECT * from Tb_test FOR XML raw (' FSF ')

3. Path mode: Allows users to customize nested XML structures, elements, attribute values through simple XPath syntax

SELECT * from Tb_test FOR XML path (' FSF ')

4. Explicit mode: Defines the structure of the output XML through the SELECT syntax

Two: Forxmlpath Application example

Tab_users:

Tab_items:

1. 2. Forxmlpath

      SELECT * from Tab_users for XML PATH

   4. Forxmlpath (' Param ')

      SELECT * from Tab_users for XML PATH (' User ')

   6. Forxmlpath + AS

      SELECT UserID as id,lastname as surname, FirstName as name from Dbo.tab_users for XML PATH (' user ')

   7. The ultimate Big trick

      SELECT UserID as ID,

      LastName as surname,

      FirstName as name,

      (SELECT CONCAT (ItemName, ', ') from the Dbo.tab_items WHERE userId =1 for XML PATH (")) As Items

      From Dbo.tab_users

      WHERE UserID = 1

      We can see from the query results that we implemented the item with a userid of 1 in the Tab_items table through Forxmlpath, and finally a comma, and we can use the result of the query with the trim (",") of the string. The extension method is removed and can then be split as needed, or the last comma can be truncated by using the left () method in the database to intercept the string.

   8. The Ultimate Big recruit supplement

      SELECT *,

      Left (A.items, LEN (A.items)-1) as goods

      From (SELECT UserID as ID,

      LastName as surname,

      FirstName as name,

      (SELECT CONCAT (ItemName, ', ')

      From Dbo.tab_items

      WHERE userId = 1

      For

      XML PATH (")

      ) as Items

      From Dbo.tab_users

      WHERE UserID = 1

      ) as A;

      Query Result:

SQL Server Forxmlpath Simple application