For XML Path

/*

 

For XML Path

We have already discussed the use of for XML (Auto, raw). The following describes the use of the for XML Path mode.

The auto and raw modes can meet most XML format requirements. The path mode can provide other format functions.

*/

 

-- 1. control level

/*

In the path mode, you can generate the XML node hierarchy. The XML hierarchy is controlled by column aliases.

The following query adds a new node info that includes name, phone, and sex. Note that "/" in the alias "/"

Is used to control the XML generation level.

*/

 

-- The test table follows the test table employees in for XML (Auto, raw.

 

Select

Employeeid as '@ id ',

Name as 'info/name ',

Phone As 'info/phone ',

Sex as 'info/sex'

From employees for XML Path ('Employee'), type, elements, root ('ployees ')

 

/*

<Employees>

<Employee ID = "1">

<Info>

<Name> Tudou </Name>

<Phone> 15958944683 </phone>

<Sex> male </sex>

</INFO>

</Employee>

<Employee ID = "2">

<Info>

<Name> shuige </Name>

<Phone> 00000000000 </phone>

<Sex> male </sex>

</INFO>

</Employee>

<Employee ID = "3">

<Info>

<Name> Xiaozhu </Name>

<Phone> 11111111111 </phone>

<Sex> male </sex>

</INFO>

</Employee>

</Employees>

*/

 

-- 2: generate a list

 

/*

We can see that the path can be used to control the hierarchy, and the path can also be used to generate a list.

It is useful when performing column conversion. You should often see this usage when visiting csdn. Below

Generate a list of employeeids

*/

 

 

 

Select

Employeeid as 'data ()'

From employees

For XML Path ('')

 

/*

Result:

1 2 3

*/

 

/*

We can see that the results are separated by spaces, but most of the time we need to use certain symbols.

Separated by commas.

*/

 

Select Replace (

(Select

Employeeid as 'data ()'

From employees

For XML Path ('')),'',',')

 

/*

Result:

1, 2, 3

*/

For XML Path