XML query in SQLSERVER: FORXML specifies RAW

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.