MyException - 我的异常网
当前位置:我的异常网» 数据库 » 微软认证考试70-461 Work with Data 数据处理 -27%

微软认证考试70-461 Work with Data 数据处理 -27%比重-(5.3)

www.MyException.Cn  网友分享于:2013-02-20  浏览:7次
微软认证考试70-461 Work with Data 数据处理 --27%比重--(5.3)

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

  1. Create Database Objects创建数据库对象 (24%)
  2. Work with Data数据处理 (27%)
  3. Modify Data数据修改 (24%)
  4. 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索引。

FOX XML指定EXPLICIT

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') ] ]

FOX XML指定EXPLICIT

一个简单的EXPLICIT例子:

with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name]
union all
select 2,1,null,'女装',null
union all
select 3,2,null,null,'上衣'
union all
select 3,2,null,null,'裤子'
)
select * from TestXmlExplicit

结果:

tag parentclothes!1!categorysubcategory!2!namesubsubcategory!3!name
1 NULL 衣服                          NULL                         NULL
2 1         NULL                         女装                         NULL
3 2         NULL                         NULL                        上衣
3 2         NULL                         NULL                         裤子

执行FOR XML EXPLICIT结果(就是上述SQL后添加FOR XML EXPLICIT):

<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory name="上衣" />
    <subsubcategory name="裤子" />
  </subcategory>
</clothes>

分析FOR XML EXPLICIT:

TAG和PARENT列是必须的。PARENT为NULL或0的是根目录。列名上叹号间的数字对应TAG。

拿三级目录来说,在一级和二级目录对应列上设为NULL值即可,因为三级目灵只关心三级目录列就行了。如TAG为3的两行,clothes!1!categorysubcategory!2!name没有对应值。

任意指定同级目录

TAG数字不代表目录级别!下面是新同级目录的例子:
with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name],null as [newcategoryname!4!name]
union all
select 2,1,null,'女装',null,null
union all
select 3,2,null,null,'上衣',null
union all
select 4,2,null,null,null,'裤子'
)
select * from TestXmlExplicit for xml explicit
结果:
<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory name="上衣" />
    <newcategoryname name="裤子" />
  </subcategory>
</clothes>
可以看出,三级目录中的元素名称已经更改成newcategoryname 。

为所有目录添加新属性

现在给每级目录添加属性ID,新SQL语句如下:

with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],1111 as [clothes!1!id],null as [subcategory!2!name],null as [subcategory!2!id],null as [subsubcategory!3!name],null as [subsubcategory!3!id]
union all
select 2,1,null,null,'女装',2222,null,0
union all
select 3,2,null,null,null,0,'上衣',3331
union all
select 3,2,null,null,null,0,'裤子',3332
)
select * from TestXmlExplicit

结果:

tag parentclothes!1!categoryclothes!1!idsubcategory!2!namesubcategory!2!idsubsubcategory!3!namesubsubcategory!3!id
1 NULL 衣服                          1111                         NULL                  NULL                        NULL                              NULL
2 1         NULL                          NULL                        女装                2222                        NULL                                  0
3 2         NULL                            NULL                        NULL                 0                               上衣                                    3331
3 2         NULL                         NULL                        NULL                 0                               裤子                                        3332

运行FOR XML EXPLICIT,结果:

<clothes category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory name="上衣" id="3331" />
    <subsubcategory name="裤子" id="3332" />
  </subcategory>
</clothes>

指定ELEMENT

with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],1111 as [clothes!1!id],null as [subcategory!2!name],null as [subcategory!2!id],null as [subsubcategory!3!name!element],null as [subsubcategory!3!id!element]
union all
select 2,1,null,null,'女装',2222,null,0
union all
select 3,2,null,null,null,0,'上衣',3331
union all
select 3,2,null,null,null,0,'裤子',3332
)
select * from TestXmlExplicit for xml explicit
红色字体部分指定了element,结果:
<clothes category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory>
      <name>上衣</name>
      <id>3331</id>
    </subsubcategory>
    <subsubcategory>
      <name>裤子</name>
      <id>3332</id>
    </subsubcategory>
  </subcategory>
</clothes>

指定ELEMENTXSINIL

with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],1111 as [clothes!1!id],null as [subcategory!2!name],null as [subcategory!2!id],null as [subsubcategory!3!name!element],null as [subsubcategory!3!id!ELEMENTXSINIL]
union all
select 2,1,null,null,'女装',2222,null,0
union all
select 3,2,null,null,null,0,'上衣',3331
union all
select 3,2,null,null,null,0,'裤子',null
)
select * from TestXmlExplicit for xml explicit

注意红色字体部分,最后一列指定了ELEMENTXSINIL,为了测试,把最后一行最后一列的值改为NULL。
结果:
<clothes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory>
      <name>上衣</name>
      <id>3331</id>
    </subsubcategory>
    <subsubcategory>
      <name>裤子</name>
      <id xsi:nil="true" />
    </subsubcategory>
  </subcategory>
</clothes>

指定 ID 和 IDREF 

with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'乐可乐可的部落格' as [clothes!1!category],'011' as [clothes!1!cloid!id],'099' as [clothes!1!cloidref!idref]


)
select * from TestXmlExplicit for xml explicit,xmldata
红色字体为XML指定了ID和IDREF类型,通XMLDATA查看架构可知:

<Schema name="Schema6" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="clothes" content="mixed" model="open">
    <AttributeType name="category" dt:type="string" />
    <AttributeType name="cloid" dt:type="id" />
    <AttributeType name="cloidref" dt:type="idref" />
    <attribute type="category" />
    <attribute type="cloid" />
    <attribute type="cloidref" />
  </ElementType>
</Schema>
<clothes xmlns="x-schema:#Schema6" category="乐可乐可的部落格" cloid="011" cloidref="099" />

指定 IDREFS 指令

with TestXmlExplicit
as
(
select 1 as tag,0 as parent,null as [clothes!1!cloidlist!idrefs]
union all
select 1,0,'022'
union all
select 1,0,'025'


)
select * from TestXmlExplicit for xml explicit,xmldata
结果:
<Schema name="Schema4" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="clothes" content="mixed" model="open">
    <AttributeType name="cloidlist" dt:type="idrefs" />
    <attribute type="cloidlist" />
  </ElementType>
</Schema>
<clothes xmlns="x-schema:#Schema4" cloidlist="022 025"></clothes>
示例中cloidlist的类型是idrefs。IDREFS类型的元素也有多个值。因此,必须使用单独的 SELECT 子句来重复使用相同的标记、父级和键列信息。然后,ORDER BY 必须确保组成IDREFS 值的行的序列成组显示在它们的父元素下。

指定 HIDE 

HIDE顾名思义,隐藏信息。
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name!hide]
union all
select 2,1,null,N'女装',null
union all
select 3,2,null,null,N'上衣'
union all
select 3,2,null,null,N'裤子'
)
select * from TestXmlExplicit for xml explicit
三级目录的subsubcategory!3!name不会显示在结果中,但是HIDE的列可以用来排序。
结果:
<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory />
    <subsubcategory />
  </subcategory>
</clothes>

指定ELEMENT和XML的不同

指定ELEMENT会实体化数据即对特殊字符进行转义,XML则不会。
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name!element],null as [subcategory!2!name2!xml]
union all
select 2,1,null,N'<b>女装</b>',N'<b>男装</b>'
)
select * from TestXmlExplicit for xml explicit
结果:
<clothes category="衣服">
  <subcategory>
    <name>&lt;b&gt;女装&lt;/b&gt;</name>
    <name2>
      <b>男装</b>
    </name2>
  </subcategory>
</clothes>

指定CDATA

with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name!cdata]
union all
select 2,1,null,N'<b>女装</b>'
)
select * from TestXmlExplicit for xml explicit
结果:
<clothes category="衣服">
  <subcategory>
    <name><![CDATA[<b>女装</b>]]></name>
  </subcategory>
</clothes>

指定XMLTEXT

with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as [subcategory!2!!XMLTEXT]
union all
select 2,1,null,N'女装',N'<s NAME="test" id="12">女装补充</s>'
)
select * from TestXmlExplicit for xml explicit
结果:
<clothes category="衣服">
  <subcategory NAME="女装" id="12">女装补充</subcategory>
</clothes>
  • 因为对于红色字体 列没有指定 AttributeName,而指定了xmltext 指令,所以 <s>元素中的属性被追加到包含它的 <subcategory> 元素(即subcategory!2!NAME,不是clothes!1!category)的属性列表中。

  • 因为 <xmltext> 元素中的 NAME属性与相同元素级上检索到的 NAME 属性冲突,所以忽略 <xmltext> 元素中的此属性,即使 NAME 为 NULL 也是如此。通常情况下,属性将覆盖溢出中具有相同名称的属性。但是id属性没有冲突,所以保留。

再看另一种情况:
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as [subcategory!2!!XMLTEXT]
union all
select 2,1,null,N'女装',N'<s NAME="test" id="12"><name>子元素</name></s>'
)
select * from TestXmlExplicit for xml explicit
XMLTEXT列值包含了子元素name, 因此当前元素追加到<subcategory>,<name>子元素</name>则作为子元素追加到<subcategory>,结果:
<clothes category="衣服">
  <subcategory NAME="女装" id="12">
    <name>子元素</name>
  </subcategory>
</clothes>
再看:
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as [subcategory!2!subname!XMLTEXT]
union all
select 2,1,null,N'女装',N'<s NAME="test" id="12"><name>子元素</name></s>'
)
select * from TestXmlExplicit for xml explicit
如果为XMLTEXT指定AttributeName(如subname),则<s>的属性追加到<subname>,然后作为子元素追加到<subcategory>,结果:
<clothes category="衣服">
  <subcategory NAME="女装">
    <subname NAME="test" id="12">
      <name>子元素</name>
    </subname>
  </subcategory>
</clothes>



第5.1部分

第5.2部分

第5.4部分

第5.5部分




文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有