微软认证考试70-461 Work with Data 数据处理 --27%比重--（5.2）

nainai1

附注：微软认证考试70-461范围

• Create Database Objects创建数据库对象 (24%)
• Work with Data数据处理 (27%)
• Modify Data数据修改 (24%)
• Troubleshoot & Optimize故障排解及SQL优化 (25%)
  

本文是第二节Work with Data 数据处理 。

第一部分直通车

第二部分直通车

第三部分直通车

第四部分直通车

第五部分：Query and manage XML data. May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export xml; xml indexing. XML数据的查询与管理。可能包含但不仅限于：理解XML类型，架构，交互，局限性和约束性；实现XML架构和XML数据的处理；了解什么时候使用XML比较恰当，包括XML命名空间；导入导出XML；XML索引。

FOR XML指定RAW,AUTO

FOR XML 模式可以是 RAW、AUTO、EXPLICIT 或 PATH。它确定产生的 XML 的形状。基本语法：

[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
    {
      { RAW [ ('ElementName') ] | AUTO }
        [
           <CommonDirectives>
           [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
           [ , ELEMENTS [ XSINIL | ABSENT ]
        ]
      | EXPLICIT
        [
           <CommonDirectives>
           [ , XMLDATA ]
        ]
      | PATH [ ('ElementName') ]
        [
           <CommonDirectives>
           [ , ELEMENTS [ XSINIL | ABSENT ] ]
        ]
     }

<CommonDirectives> ::=
   [ , BINARY BASE64 ]
   [ , TYPE ]
   [ , ROOT [ ('RootName') ] ]

FOR XML 指定RAW

with TestXml
as
(
    select 1 as id,'乐可乐可的部落格' as name
    union all
    select 2,'微软认证考试70-461'
    union all
    select 3 ,'FOR XML'
)
select id,name from testxml for xml raw,type

结果：

<row id="1" name="乐可乐可的部落格" />
<row id="2" name="微软认证考试70-461" />
<row id="3" name="FOR XML" />

红色字体type可选，不会影响结果，只是影响数据类型。

指定 ELEMENTS：

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,elements

注意，第三行值改为NULL值进行测试。

结果：

<row>
  <id>1</id>
  <name>乐可乐可的部落格</name>
</row>
<row>
  <id>2</id>
  <name>微软认证考试70-461</name>
</row>
<row>
  <id>3</id>
</row>

元素name在第三行没有出现，因为是NULL值。用XSINIL生成NULL值的name元素。

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,elements XSINIL
结果：

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1</id>
  <name>乐可乐可的部落格</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>2</id>
  <name>微软认证考试70-461</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>3</id>
  <name xsi:nil="true" />
</row>

使用XMLDATA和XMLSCHEMA

XMLDATA返回描述文档结构的 XML-DATA 架构

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,XMLDATA  结果：

<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="乐可乐可的部落格" />
<row xmlns="x-schema:#Schema2" id="2" name="微软认证考试70-461" />
<row xmlns="x-schema:#Schema2" id="3" />

通过指定 XMLSCHEMA 选项，您可以针对结果请求 XSD 架构:

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA   
结果：

<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="乐可乐可的部落格" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="2" name="微软认证考试70-461" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="3" />

您可以将目标命名空间 URI 指定为 FOR XML 中 XMLSCHEMA 的可选参数。

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA ('urn:http://blog.iyunv.com/lihui_830501')
结果：

<xsd:schema targetNamespace="urn:http://blog.iyunv.com/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.iyunv.com/lihui_830501" id="1" name="乐可乐可的部落格" />
<row xmlns="urn:http://blog.iyunv.com/lihui_830501" id="2" name="微软认证考试70-461" />
<row xmlns="urn:http://blog.iyunv.com/lihui_830501" id="3" />

检索二进制数据

像XMLDATA一样，在SQL中指定BINARY BASE64。

重命名 <row> 元素

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw ('myrow')

结果：

<myrow id="1" name="乐可乐可的部落格" />
<myrow id="2" name="微软认证考试70-461" />
<myrow id="3" />

指定ELEMENTS的情况类同。

为 FOR XML 生成的 XML 指定根元素

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml raw,root('myroot')

结果：

<myroot>
  <row id="1" name="乐可乐可的部落格" />
  <row id="2" name="微软认证考试70-461" />
  <row id="3" />
</myroot>

查询 XML 类型的列

declare @xml table(xid int,xname varchar(50),xmlcol xml);
insert into @xml select 1,'第一行','<myroot>
  <row id="1" name="乐可乐可的部落格" />
  <row id="2" name="微软认证考试70-461" />
  <row id="3" />
</myroot>'
insert into @xml select 2,'第二行','<myroot>
  <row id="4" name="XML数据" />
  <row id="5" name="微软考试" />
</myroot>'
select xid,xname,xmlcol.query('/myroot') from @xml for xml raw

结果：

<row xid="1" xname="第一行">
  <myroot>
    <row id="1" name="乐可乐可的部落格" />
    <row id="2" name="微软认证考试70-461" />
    <row id="3" />
  </myroot>
</row>
<row xid="2" xname="第二行">
  <myroot>
    <row id="4" name="XML数据" />
    <row id="5" name="微软考试" />
  </myroot>
</row>

FOR XML 指定AUTO

with TestXml
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
    union all
    select 3 ,null
)
select id,name from testxml for xml auto 结果：

<testxml id="1" name="乐可乐可的部落格" />
<testxml id="2" name="微软认证考试70-461" />
<testxml id="3" />

用表名做元素名称，即替代RAW模式中的“row”。

下面看多表的查询

with [order]
as
(
    select 122 as orderid, 1 as productid,10 as quantity
    union all
    select 123,1 as productid,100 as quantity
    union all
    select 124,2,20
    union all
    select 125,3 ,5
),
product
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
)
select * from product,[order] where [order].productid=product.id for xml auto

结果：

<product id="1" name="乐可乐可的部落格">
  <order orderid="122" productid="1" quantity="10" />
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
  <order orderid="124" productid="2" quantity="20" />
</product>

粗体部分顺序敏感。如果把product和order换一下位置，结果：

<order orderid="122" productid="1" quantity="10">
  <product id="1" name="乐可乐可的部落格" />
</order>
<order orderid="123" productid="1" quantity="100">
  <product id="1" name="乐可乐可的部落格" />
</order>
<order orderid="124" productid="2" quantity="20">
  <product id="2" name="微软认证考试70-461" />
</order>

当然，AUTO模式同样也可以指定ELEMENTS，BINARY BASE64，同RAW。

返回的 XML 成形过程中的 AUTO 模式试探方法

AUTO 模式根据查询决定返回的 XML 的形式。 在决定嵌套元素的方式时，AUTO 模式试探方法会比较相邻行中的列值。ntext、text、image 和xml 类型以外的所有类型的列都会进行比较。 (n)varchar(max) 和varbinary(max) 类型的列也会进行比较。

上面的第一个指定AUTO的SQL语句结果集为：

id    name                                  orderid    productid    quantity
1    乐可乐可的部落格                122               1             10
1    乐可乐可的部落格                123               1              100
2    微软认证考试70-461           124               2              20

AUTO 模式试探方法将比较表 product 的所有值（Id 列和 Name 列）。因为前两行的 Id 列和 Name 列具有相同的值，所以向结果中添加了一个具有两个 <order> 子元素的 <product> 元素。

<product id="1" name="乐可乐可的部落格">
  <order orderid="122" productid="1" quantity="10" />
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
  <order orderid="124" productid="2" quantity="20" />
</product>

如果把Name 列改为 text 类型。 AUTO 模式试探方法不比较此类型的值， 而是认为这些值不相同。

declare @order table(orderid int,productid int,quantity int)
declare @product table(id int,name text)
insert into @order
select 122 as orderid, 1 as productid,10 as quantity
    union all
    select 123,1 as productid,100 as quantity
    union all
    select 124,2,20
    union all
    select 125,3 ,5
insert into @product
select 1 ,N'乐可乐可的部落格'
    union all
    select 2,N'微软认证考试70-461'
select * from @product as product,@order as [order] where [order].productid=product.id for xml auto

结果：

<product id="1" name="乐可乐可的部落格">
  <order orderid="122" productid="1" quantity="10" />
</product>
<product id="1" name="乐可乐可的部落格">
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
  <order orderid="124" productid="2" quantity="20" />
</product>

再看第一个指定AUTO的SQL语句，但是更改了orderid：

with [order]
as
(
    select 122 as orderid, 1 as productid,10 as quantity
    union all
    select 125,1 as productid,100 as quantity
    union all
    select 123,2,20
    union all
    select 124,3 ,5
),
product
as
(
    select 1 as id,N'乐可乐可的部落格' as name
    union all
    select 2,N'微软认证考试70-461'
)
select * from product,[order] where [order].productid=product.id
order by orderid

结果：

id    name                               orderid    productid    quantity
1    乐可乐可的部落格            122               1              10
2    微软认证考试70-461       123               2              20
1    乐可乐可的部落格            125               1              100

这样相同id和name的product没有连在一起，AUTO模式试探将生成以下结果：

<product id="1" name="乐可乐可的部落格">
  <order orderid="122" productid="1" quantity="10" />
</product>
<product id="2" name="微软认证考试70-461">
  <order orderid="123" productid="2" quantity="20" />
</product>
<product id="1" name="乐可乐可的部落格">
  <order orderid="125" productid="1" quantity="100" />
</product>

参考：http://msdn.microsoft.com/zh-cn/library/ms178107.aspx

y23335793

帮你顶下哈！！

angela

听君一席话，省我十本书!

鸬鹚洲

禽兽尚且有半点怜悯之心，而我一点也没有，所以我不是禽兽。

超酷小

站的更高，尿的更远。

nikoo

看尽天下A片，心中自然无码～

超酷小

解释就是掩饰，掩饰就是编故事!