XML query in SQL SERVER: FOR XML specifies PATH

XML query in SQL SERVER: FOR XML specifies PATH
Preface

In SQL SERVER, you can specify RAW, AUTO, EXPLICIT, and PATH for XML queries. This article uses some examples to introduce the XML query of the specified PATH in SQL SERVER.

PATH Parameter
PATH ('parameter'). The parameter is used to rename the ROW, and the ROW is the default name. Select 'hui' for xml path result: <row> Hui </row>
Select 'hui' for xml path ('root') Result: <root> Hui </root>

Columns without names
Select 'hui Lil' for xml path
Result: <row> Hui Li </row>
Columns with names
The column name starts with the @ symbol.
Select 'hui Lil' as [@ name] for xml path result: <row name = "Hui Li"/>
The column name does not start with the @ symbol

Select 'hui Lil' as [name] for xml path result:
<Row>
<Name> Hui Li </name>
</Row>

The column name does not start with the @ symbol and contains the 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], 'lil' as [name/last] for xml path result:
<Row>
<Name>
<First> Hui </first>
<Last> Li </last>
</Name>
</Row> be case sensitive.

Multiple columns with the same prefix are interrupted.
Select 'hui' as [name/first], 'Chicago 'as [address], 'lil' as [name/last] for xml path result:
<Row>
<Name>
<First> Hui </first>
</Name>
<Address> Chicago </address>
<Name>
<Last> Li </last>
</Name>
</Row>


Column named as a wildcard
Select 'hui' as [*], ''as [*], 'lil' as [*] for xml path
Select 'hui', '', 'lil' for xml path
The execution results of the preceding two SQL statements are the same: <row> Hui Li </row>
If the data type is XML, insert the XML tree as a child element. Declare @ table (name varchar (50), xmlcontent xml)
Insert into @ table select 'hui', '<root> <person> </root>'
Select name, xmlcontent as [*] from @ table for xml path
Result: <row>
<Name> Hui </name>
<Root>
<Person/>
</Root>
</Row>
Column name: column tested by the XPath Node

Column name	Action
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 this column is added as an XML comment.
Node ()	For a column named node (), the result is the same as that of the column named.
Processing Instruction (name)	If the column name is a processing command, the string value in the column will be added to the PI value of the Target name of the Processing Command.

Example: select 'hui' as [first/text ()], 'lil' 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>
Name of the column with the path specified as data ()
If the path specified as the column name is data (), the value is processed as an atomic value in the generated XML. If the next item in serialization is also an atomic value, a space character will be added to the XML. This is useful when creating typed element values and attribute values in the list.
With T
As
(
Select 11 as id
Union all
Select 22
Union all
Select 33
)
Select id as [data ()] from T for xml path ('')
Result: 11 22 33
Here ('') can be understood as setting the name of the XML root element to null.
NULL Value column
Select 'hui' as [name], null as [address] for xml path
Result: <row>
<Name> hui </name>
</Row>
There is no relevant address content. After elements xsinil is specified, select 'hui' as [name], null as [address] for xml path, ELEMENTS XSINIL
Result: <row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">
<Name> hui </name>
<Address xsi: nil = "true"/>
</Row>
Supported namespaces in PATH Mode
With xmlnamespaces (n' happy blog 'as)
SELECT 1 as 'a: B'
FOR XML PATH
Result: <row xmlns: a = "Happy blog">
<A: B> 1 </a: B>
</Row>
Summary
The above describes the XML query for the specified EXPLICIT. So far Specify RAW, AUTO, EXPLICIT, and PATHThe XML query is complete.

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 XML indexes, see: http://blog.csdn.net/leewhoee/article/details/8579743