FOR XML子句有四种最基本的形式,如上图所示:
1、AUTO形式:回来数据表为起表名的元素,每一列的值回来为特点;
2、RAW形式:回来数据行为元素,每一列的值作为元素的特点;
3、PATH形式:经过简略的XPath语法来答使用户自界说嵌套的XML结构、元素、特点值
4、EXPLICIT形式:经过SELECT语法界说输出XML的结构
详细实例如下:
1、AUTO形式
(1). SQL句子:
1: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML AUTO, XMLSCHEMA |
(2). 所生成的XML文件:
回来XML文件的XML Schema
<xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified">
<xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
2、RAW形式
1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee') (2). 所生成的XML文件: 1: <Employee EmployeeID="1" FirstName="Nancy" LastName="Davolio" /> 2: <Employee EmployeeID="2" FirstName="Andrew" LastName="Fuller" /> 3: <Employee EmployeeID="3" FirstName="Janet" LastName="Leverling" /> 4: <Employee EmployeeID="4" FirstName="Margaret" LastName="Peacock" /> 5: <Employee EmployeeID="5" FirstName="Steven" LastName="Buchanan" /> 6: <Employee EmployeeID="6" FirstName="Michael" LastName="Suyama" /> <xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 0 <xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 1 <xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 2 |
3、PATH形式:
(1). SQL句子:
<xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 3 <xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 4 |
(2). 所生成的XML文件
<xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified">
<xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
5
4、EXPLICIT形式
问题:参加要生成如下的XML文档该怎么操作?
Nancy Davolio EXPLICIT形式处理这个问题的使用分为两个首要立刻
1.界说要输出的XML文档结构;
2.传入实践的数据值;
(1). SQL句子:
<xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 8 <xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"> <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/> <xsd:elementname="Employees"> <xsd:complexType> <xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/> <xsd:attributename="FirstName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="10"/> xsd:restriction> xsd:simpleType> xsd:attribute> <xsd:attributename="LastName"use="required"> <xsd:simpleType> <xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"> <xsd:maxLengthvalue="20"/> xsd:restriction> xsd:simpleType> xsd:attribute> xsd:complexType> xsd:element> xsd:schema> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/> <Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/> 9 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')0 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')1 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')2 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')3 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')4 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')5 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')6 1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')7 |
句子意义的解说:
先看看界说XML结构的句子输出成果:
Tag栏用来指定生成元素的嵌套水平;1表明嵌套水平为
Parent栏用来指定当时Tag的父级层次;Null值表明该元素为***元素;
EmployeeID AS [Employee!1!EmpID],
阐明:当时元素或特点的父级元素的称号!元素的标签号!元素或特点的称号
FirstName AS [Employee!1!FirstName!element],
阐明:当时元素或特点的父级元素的称号!元素的标签号!元素或特点的称号!指定值作为元素输出
5、为输出的XML文档增加根元素(Root element)
(1). SQL句子:
1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')8 |
(2). 所生成的XML文件
1: ----将元素命名为自界说的称号Employee2: SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')9
【修改引荐】
- SQL Server夏天数据库处理方法
- SQL Server 2005完成数据库缓存依靠
- 详解在不同的SQL Server数据库之间查询数据