Usage of SQL Server FOR XML PATH and STUFF functions

Keywords: SQL Server xml SQL

Brief introduction

Next, we use this method to query the data of this table.

　　  select * from [dbo].[Workflow_Action] for xml path
It can convert query results into a piece of code in XML format

　　　　

　　　--for xml path You can write after that. for xml path(Schema)，If you write this, you can use nodes<row>Become<Schema>
　　  select WorkflowSchema as WS,ActionSchema as SC,ActionName as AN from [dbo].[Workflow_Action] for xml path('Schema')

       

　　  --We can also output the value of a field individually.
　　  SELECT '[ '+ActionName+' ]' FROM [dbo].[Workflow_Action] FOR XML PATH('')

　　　　

2. Practical application

　　  --Let's look at a multi-grid process corresponding to an operation.
　　  --There are two floors altogether, one of which is found to be separate. ActionName，Put it together in a row and use it where Conditional Connection Outer Layer
　　  SELECT WorkflowSchema,
　　    (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
  　　　　WHERE WorkflowSchema=A.WorkflowSchema    --Conditions that must be added
  　　　　FOR XML PATH('')) AS ActionList
　　  FROM [dbo].[Workflow_Action] A 
　　  GROUP BY WorkflowSchema
　　　　

　　  --where The connection condition must be removed, and if it is removed, all of it will be found. ActionName，As in the example above
　　　　

　　  --Now let's optimize the format, and you'll find that there's one more.','Symbols LEFT The function removes him and continues to query outside.
　　  select B.WorkflowSchema,
　　　　　　　 LEFT(B.ActionList,LEN(B.ActionList)-1) as ActionList 
　　　　　　　　　　 from (
　　     　　　　　　　　　　SELECT WorkflowSchema,
　　　　　　　　　　　　　　　　　　　(SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
  　　　　　　　　　　　　　　　　    WHERE WorkflowSchema=A.WorkflowSchema 
  　　　　　　　　　　　　　　　　　　 FOR XML PATH('')) AS ActionList
　　　　  　　　　　　　　　　FROM [dbo].[Workflow_Action] A 
　　　　GROUP BY WorkflowSchema) as B

　　　　 

Next, let's talk about another function to achieve the same effect, the STUFF function.

The sql stuff function is used to delete characters of specified length and insert another set of characters at the specified starting point. The sql stuff function returns an empty string if the starting position or length value is negative, or if the starting position is greater than the length of the first string. If the length to be deleted is greater than the length of the first string, the first character in the first string will be deleted.

Role

Delete the specified length of characters and insert another set of characters at the specified starting point.

Grammar

　　　　　　STUFF ( character_expression , start , length ,character_expression )

Parameters

character_expression is a character data expression. character_expression can be a constant, a variable, a character column or a binary data column.

Start An integer value that specifies the starting position for deletion and insertion. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Start can be a bigint type.

Length An integer specifying the number of characters to be deleted. If length is longer than the first character_expression, the last character in the last character_expression is deleted at most. Length can be of bigint type.

Return type

If character_expression is a supported character data type, character data is returned. If character_expression is a supported binary data type, the binary data is returned.

3. Remarks

1. If the starting position or length value is negative, or if the starting position is greater than the length of the first string, an empty string will be returned. If the length to be deleted is greater than the length of the first string, the first character in the first string will be deleted.

2. Errors occur if the result value is greater than the maximum value supported by the return type.

4. sql stuff function

Example 1
 select STUFF('abcdefg',1,0,'1234') -- The result is'1234 abcdefg'
select STUFF('abcdefg',1,1,'1234') -- The result is'1234 bcdefg'
select STUFF('abcdefg',2,1,'1234') -- The result is'a1234cdefg'
select STUFF('abcdefg',2,2,'1234') -- the result is'a1234 defg'

　　　　--Example two SQL Converting columns to strings and separating them with commas
　　　　--Similarly, we use it. for xml path This method
　　　　SELECT STUFF((SELECT ',' + ActionName FROM [dbo].[Workflow_Action] FOR XML PATH('')),1,1,'') AS WA

　　　　　

　　　　--Example 3. Finally, we implement the above. for xml path Function
　　　　--Find out two fields first, and then pair them with ActionName This field is transformed. where If you remember to add the condition, it will show all of you if you don't add it. ActionName
　　　　select WorkflowSchema,
       　　　　ActionName=(STUFF((select ',' + ActionName 
                          　　　　from [dbo].[Workflow_Action] a 
                          　　　　where a.WorkflowSchema=b.WorkflowSchema for xml path('')),1,1,''))    --where Conditions must be added 
　　　　from [dbo].[Workflow_Action] b group by WorkflowSchema

　　　　 

Comparing the above two approaches, we can compare which one is more convenient.