SQL for XML PATH and Stuff usage

SQL Stuff Usage

2. Use

Deletes a character of the specified length and inserts another set of characters at the specified starting point.

2. Grammar

STUFF (character_expression, start, length, character_expression)

3. Example

The following example removes the three characters from the 2nd position (character B) in the first string abcdef, and then inserts a second string at the beginning of the deletion, creating and returning a string

SELECT STUFF (' abcdef ', 2, 3, ' IJKLMN ')
GO

Here is the result set

Aijklmnef

4. Parameters
character_expression

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

Start

An integer value that specifies the start position of the delete and insert. 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 that specifies the number of characters to delete. If length is longer than the first character_expression, it is removed up to the last character in the last character_expression. Length can be a bigint type.

5. Return type
If Character_expression is a supported character data type, character data is returned. If character_expression is a supported binary data type, binary data is returned.

6. Remarks
If the result value is greater than the maximum value supported by the return type, an error is generated.

--------------------------------------------------------------------------------------------------------------- ------------------------------------------

FOR XML PATH Some people may know that some people may not know, in fact, it is the query result set is presented in XML form, with it we can simplify our query statement implementation of some previously may need to rely on the function of a live stored procedure to complete the work. Then take one instance as the main.

A. For XML PATH Brief introduction

So let's start with the for XML PATH, assuming that there is now a hobby table (hobby) to store interests, the table structure is as follows:

Next, let's look at the query result statement that applies the for XML path as follows:

SELECT *  from @hobby  for XML PATH

Results:

<Row>  <Hobbyid>1</Hobbyid>  <Hname>Climbing</Hname></Row><Row>  <Hobbyid>2</Hobbyid>  <Hname>Swimming</Hname></Row><Row>  <Hobbyid>3</Hobbyid>  <Hname>Food</Hname></Row>

This shows that the FOR XML PATH can output query results according to the row into XML all kinds! So, how do you change the name of the XML row node? The code is as follows:

SELECT *  from @hobby  for XML PATH ('myhobby')

The results must have been imagined, right? That's right. The original line node <row> became our custom name <myhobby> in parentheses () after the path, and the result is as follows:

<Myhobby>  <Hobbyid>1</Hobbyid>  <Hname>Climbing</Hname></Myhobby><Myhobby>  <Hobbyid>2</Hobbyid>  <Hname>Swimming</Hname></Myhobby><Myhobby>  <Hobbyid>3</Hobbyid>  <Hname>Food</Hname></Myhobby>

This time the careful friend must ask then how does the column node change? Do you remember the keyword as that lists the aliases? Yes, that's it! The code is as follows:

SELECT  as ' MyCode '  as ' MyName '  from @hobby  for XML PATH ('myhobby')

So at this point the node name of our column will also be programmed with our custom name <MyCode> and <MyName> results as follows:

<Myhobby>  <MyCode>1</MyCode>  <MyName>Climbing</MyName></Myhobby><Myhobby>  <MyCode>2</MyCode>  <MyName>Swimming</MyName></Myhobby><Myhobby>  <MyCode>3</MyCode>  <MyName>Food</MyName></Myhobby>

Oh! Now that we can customize the nodes and columns of the row, can we build the output that we like? Or look at the code:

SELECT '  '+hname+'  ]'from@hobby  for XML PATH (")

Yes, we can also define the output format of a String type field by using the symbol + number. The results are as follows:

[][][]

So how do other types of columns customize? That's OK, let's convert them to string type! For example:

SELECT ' {'+STR(Hobbyid)+'} ','+hname+'  ]'  from @hobby  for XML PATH (")

Good for XML path is basically introduced here, more about the FOR XML knowledge please consult the Help document!

Let's take a look at an application scenario for XML path! So let's start ...

Two. One application scenario with the for XML path application

First of all! We are adding a student table, the column is (Stuid,sname,hobby), Stuid on behalf of the student number, SName on behalf of the student name, hobby the student's hobby! So now the table structure is as follows:

At this point, our request is to query the student table, showing the result set of all students ' hobbies, the code is as follows:

SELECTB.sname, Left(Stulist,LEN(stulist)-1) asHobby from (SELECTSName, (SELECTHobby+','  fromStudentWHERESName=A.sname forXML PATH ("')) asstulist fromStudent AGROUP  bysName) B

The results are as follows:

Analysis: OK, then let's analyze it, first look at this sentence:

SELECT Hobby+',' from student     WHERE sName=a.sname   for XML PATH (")

This sentence is through the for XML PATH to a name like Zhang San's hobby, shown in the format of: "Hobby 1, Hobby 2, Hobby 3," the format!

Then look at:

SELECTB.sname, Left(Stulist,LEN(stulist)-1) asHobby from (SELECTSName, (SELECTHobby+','  fromStudentWHERESName=A.sname forXML PATH ("')) asstulist fromStudent AGROUP  bysName) B

The rest of the code begins by grouping tables, executing the for XML PATH format, when the structure that is queried when the outermost select is not executed is:

You can see that the data in the Stulist column will have a comma, then the statement with the outer layer:SELECT b.sname, left(stulist,LEN(stulist)-1 as hobby is to remove the comma and give a meaningful reference!

SQL for XML PATH and Stuff usage