XML query in SQLSERVER: FORXML specifies RAW

XML query in SQL SERVER: FOR XML specifies the RAW Preface

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

Basic for xml query

View instances:

With TestXml
As
(
Select 1 as id, 'leewhoeeuniversity 'as name
Union all
Select 2, 'xml query in SQLSERVER'
Union all
Select 3, 'for xml'
)
Select id, name from testxml for xml raw, type

Result After running:

<Row id = "1" name = "LeeWhoeeUniversity"/>
<Row id = "2" name = "XML query in SQLSERVER"/>
<Row id = "3" name = "for xml"/>

The red font type is optional and does not affect the result. It only affects the data type.

Specify ELEMENTS:

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, elements

Note that the value of the third row is changed to NULL for testing.

Result:

<Row>
<Id> 1 </id>
<Name> LeeWhoeeUniversity </name>
</Row>
<Row>
<Id> 2 </id>
<Name> XML query in SQLSERVER </name>
</Row>
<Row>
<Id> 3 </id>
</Row>

The element name does not appear in the third row because it is a NULL value.

However, we can use XSINIL to generate the name element of the NULL value.

For example:

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, elements XSINIL
Running result:

<Row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">
<Id> 1 </id>
<Name> LeeWhoeeUniversity </name>
</Row>
<Row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">
<Id> 2 </id>
<Name> XML query in SQLSERVER </name>
</Row>
<Row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">
<Id> 3 </id>
<Name xsi: nil = "true"/>
</Row>

Use XMLDATA and XMLSCHEMA

XMLDATA returns the XML-DATA architecture that describes the document structure.

For example:

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, XMLDATA

Result:

<Schema name = "Schema2" xmlns = "urn: schemas-microsoft-com: xml-data" xmlns: dt = "urn: schemas-microsoft-com: datatypes">
<ElementType name = "row" content = "empty" model = "closed">
<AttributeType name = "id" dt: type = "i4"/>
<AttributeType name = "name" dt: type = "string"/>
<Attribute type = "id"/>
<Attribute type = "name"/>
</ElementType>
</Schema>
<Row xmlns = "x-schema: # Schema2" id = "1" name = "LeeWhoeeUniversity"/>
<Row xmlns = "x-schema: # Schema2" id = "2" name = "XML query in SQLSERVER"/>
<Row xmlns = "x-schema: # Schema2" id = "3"/>

XML SCHEMA

By specifying the XMLSCHEMA option, you can request the XSD Schema for the results:

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, XMLSCHEMA
Result:

<Xsd: schema targetNamespace = "urn: schemas-microsoft-com: SQL: SqlRowSet2" xmlns: xsd = "http://www.w3.org/2001/XMLSchema" xmlns: sqltypes = "http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault = "qualified">
<Xsd: import namespace = "http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation = "http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<Xsd: element name = "row">
<Xsd: complexType>
<Xsd: attribute name = "id" type = "sqltypes: int" use = "required"/>
<Xsd: attribute name = "name">
<Xsd: simpleType>
<Xsd: restriction base = "sqltypes: nvarchar" sqltypes: localeId = "2052" sqltypes: sqlCompareOptions = "IgnoreCase IgnoreKanaType IgnoreWidth">
<Xsd: maxLength value = "12"/>
</Xsd: restriction>
</Xsd: simpleType>
</Xsd: attribute>
</Xsd: complexType>
</Xsd: element>
</Xsd: schema>
<Row xmlns = "urn: schemas-microsoft-com: SQL: SqlRowSet2" id = "1" name = "LeeWhoeeUniversity"/>
<Row xmlns = "urn: schemas-microsoft-com: SQL: SqlRowSet2" id = "2" name = "SQLSERVER XML query"/>
<Row xmlns = "urn: schemas-microsoft-com: SQL: SqlRowSet2" id = "3"/>

You can specify the URI of the target namespace as an optional parameter of XMLSCHEMA in for xml.

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, XMLSCHEMA ('urn: http://blog.csdn.net/lihui_830501 ')

Result:

<Xsd: schema targetNamespace = "urn: http://blog.csdn.net/lihui_830501" xmlns: xsd = "http://www.w3.org/2001/XMLSchema" xmlns: sqltypes = "http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault = "qualified">
<Xsd: import namespace = "http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation = "http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<Xsd: element name = "row">
<Xsd: complexType>
<Xsd: attribute name = "id" type = "sqltypes: int" use = "required"/>
<Xsd: attribute name = "name">
<Xsd: simpleType>
<Xsd: restriction base = "sqltypes: nvarchar" sqltypes: localeId = "2052" sqltypes: sqlCompareOptions = "IgnoreCase IgnoreKanaType IgnoreWidth">
<Xsd: maxLength value = "12"/>
</Xsd: restriction>
</Xsd: simpleType>
</Xsd: attribute>
</Xsd: complexType>
</Xsd: element>
</Xsd: schema>
<Row xmlns = "urn: http://blog.csdn.net/lihui_830501" id = "1" name = "LeeWhoeeUniversity"/>
<Row xmlns = "urn: http://blog.csdn.net/lihui_830501" id = "2" name = "XML query in SQLSERVER"/>
<Row xmlns = "urn: http://blog.csdn.net/lihui_830501" id = "3"/>

Retrieving binary data

Specify BINARY BASE64 in SQL like XMLDATA.

Rename <row> element

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw ('myrow ')

Result:

<Myrow id = "1" name = "LeeWhoeeUniversity"/>
<Myrow id = "2" name = "XML query in SQLSERVER"/>
<Myrow id = "3"/>

The specified ELEMENTS is similar.

Specify the root element FOR the XML generated by FOR XML

With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'xml query in SQLSERVER'
Union all
Select 3, null
)
Select id, name from testxml for xml raw, root ('myroot ')

Result:

<Myroot>
<Row id = "1" name = "LeeWhoeeUniversity"/>
<Row id = "2" name = "XML query in SQLSERVER"/>
<Row id = "3"/>
</Myroot>

Query XML columns?

1 2 3 4 5 6 7 8 9 10 11

Declare @ xml table (xid int, xname varchar (50), xmlcol xml); insert into @ xml select 1, 'First row ', '<myroot> <row id = "1" name = "LeeWhoeeUniversity"/> <row id = "2" name = "XML query"/> <row id =" 3 "/> </myroot> 'insert into @ xml select 2, 'Second row ', '<myroot> <row id = "4" name = "XML data"/> <row id = "5" name = "for xml specify RAW"/> </myroot> 'select xid, xname, xmlcol. query ('/myroot') from @ xml for xml raw

Result:

<Row xid = "1" xname = "first line">
<Myroot>
<Row id = "1" name = "LeeWhoeeUniversity"/>
<Row id = "2" name = "XML query in SQLSERVER"/>
<Row id = "3"/>
</Myroot>
</Row>
<Row xid = "2" xname = "second row">
<Myroot>
<Row id = "4" name = "XML data"/>
<Row id = "5" name = "for xml specify RAW"/>
</Myroot>
</Row>

Summary

The above describes the XML query of the specified RAW. The next article will continue to introduce the XML query in SQL SERVER using an instance: Specify AUTO query.