XML query in SQL Server: FOR XML specifies path

XML query in SQL Server: FOR XML specifies path
Objective

In SQL Server, XML queries can specify Raw,auto,explicit,path. This article uses some examples to introduce XML queries that specify path in SQL Server.

Path number of references
PATH (' parameters '), which is used to rename the row, and row is the default generated name. Select ' Hui ' FOR XML path result:<row>hui</row>
Select ' Hui ' FOR XML path (' root ') result:<root>hui</root>

Columns without a name
Select ' Hui Li ' for XML path
Results: <row>hui li</row>
A column with a name
Column names begin with the @ sign
select ' Hui li ' as [@name] FOR XML path result: <row name= "Hui li"/>
Column names do not start with the @ sign

Select ' Hui Li ' as [name] FOR XML path result:
<row>
<name>hui li</name>
</row>

Column names do not start with the @ sign and include a slash mark (/)
Select ' Hui ' as [Name/first] FOR XML path result:
<row>
<name>
<first>Hui</first>
</name>
</row>

Multiple columns share the same prefix

Select ' Hui ' as [Name/first], ' Li ' as [name/last] FOR XML path result:
<row>
<name>
<first>Hui</first>
<last>Li</last>
</name>
</row>Note uppercase and lowercase sensitive.

Share the same prefix multiple columns are interrupted in order
Select ' Hui ' as [Name/first], ' Chicago ' as [address], ' Li ' as [name/last] FOR XML path result:
<row>
<name>
<first>Hui</first>
</name>
<address>Chicago</address>
<name>
<last>Li</last>
</name>
</row>


A column that is named as a wildcard character
Select ' Hui ' as [*], ' as [*], ' Li ' as [*] FOR XML path
Select ' Hui ', ' ', ' Li ' FOR XML path
The above two SQL statements run the same result: <row>hui li</row>
If it is an XML type, insert the XML tree as a child element. Declare @table table (name varchar (), xmlcontent XML)
Insert INTO @table select ' Hui ', ' <root><person></person></root> '
Select Name,xmlcontent as [*] from @table FOR XML path
Result:<row>
<name>Hui</name>
<root>
<person/>
</root>
</row>
Column named XPath node Test column

Column Name	Behavior
Text ()	For a column named text (), the string value in the column is added as a text node.
Comment ()	For a column named comment (), the string value in the column is added as an XML stare.
Node ()	For columns named node (), the result is the same as when the column name is a wildcard character (*).
Processing instruction (name)	Assuming the column name is a processing instruction, the string value in the column is added to the PI value of the target name for this processing instruction.

Demo sample: Select ' Hui ' as [First/text ()], ' Li ' as [Last/node ()], ' Hui li ' as [Fullname/comment ()], ' Test ' as ' Processing-instruction (PI) "FOR XML path result:<row>
<first>Hui</first>
<last>Li</last>
<fullname>
<!--Hui li-->
</fullname>
<? PI test?>
</row>
The name of the column with the path specified as data ()
assuming that the path specified as a column name is data (), the value will be treated as an atomic value in the generated XML. assuming that the next item in the serialization is also an atomic value, a space character is added to the XML. This is useful when creating list typed element values and attribute values.
With T
As
(
Select one as ID
UNION ALL
Select 22
UNION ALL
Select 33
)
Select ID as [data ()] from T to XML Path (")
Results: 11 22 33
The (') here is understood to set the root element name of the XML to null.
Null Value column
Select ' Hui ' as [name],null as [address] for XML path
Result:<row>
<name>hui</name>
</row>
No matter what the relevant address is, specify ELEMENTS xsinil after: Select ' hui ' as [name],null as [address] for XML path, ELEMENTS Xsinil
Results: <row xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" >
<name>hui</name>
<address xsi:nil= "true"/>
</row>
Namespace support in PATH mode
With XmlNamespaces (N ' Le cola available blog ' as a)
SELECT 1 as ' a:b '
For XML PATH
Results: <row xmlns:a= "le Cola blog" >
<a:b>1</a:b>
</row>
Summarize
The above XML query for the specified explicit is done. So far, in SQL Server, Specifies the raw,auto,explicit,pathThe XML query is complete for all introductions.

1. XML query in SQL Server: FOR XML specifies RAW
2. XML query in SQL Server: FOR XML specifies auto
   
3. XML query in SQL Server: FOR XML specifies explicit
   
4. XML query in SQL Server: FOR XML specifies path
5. For XML types, see: http://blog.csdn.net/leewhoee/article/details/8571286
6. For an XML index, see: http://blog.csdn.net/leewhoee/article/details/8579743

XML query in SQL Server: FOR XML specifies path