附注:微软认证考试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索引。
FOX XML指定PATH
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指定PATH
PATH参数
PATH('参数'),参数是用来重命名ROW的,ROW是默认生成的名称。
select 'Hui' for xml path 结果:<row>Hui</row>
select 'Hui' for xml path ('root') 结果:<root>Hui</root>
不带名称的列
select 'Hui Li' for xml path
结果:
<row>Hui Li</row>
具有名称的列
列名以 @ 符号开头
select 'Hui Li' as [@name] for xml path 结果:<row name="Hui Li" />
列名不以 @ 符号开头
select 'Hui Li' as [name] for xml path 结果:
<row>
<name>Hui Li</name>
</row>
列名不以 @ 符号开头并包含斜杠标记 (/)
select 'Hui' as [name/first] for xml path 结果:
<row>
<name>
<first>Hui</first>
</name>
</row>
多个列共享同一前缀
select 'Hui' as [name/first],'Li' as [name/last] for xml path 结果:
<row>
<name>
<first>Hui</first>
<last>Li</last>
</name>
</row>
注意大小写敏感。
共享同一前缀多列被打断顺序
select 'Hui' as [name/first],'Chicago' as [address],'Li' as [name/last] for xml path 结果:
<row>
<name>
<first>Hui</first>
</name>
<address>Chicago</address>
<name>
<last>Li</last>
</name>
</row>
称指定为通配符的列
select 'Hui' as [*],' ' as [*], 'Li' as [*] for xml path
select 'Hui',' ', 'Li' for xml path
上面两个SQL语句执行结果相同:
<row>Hui Li</row>
如果是XML类型,则作为一个子元素插入XML树。
declare @table table(name varchar(50), xmlcontent xml)
insert into @table select 'Hui', '<root><person></person></root>'
select name,xmlcontent as [*] from @table for xml path
结果:
<row>
<name>Hui</name>
<root>
<person />
</root>
</row>
列名为 XPath 节点测试的列
列名
行为
text()
|
对于名为 text() 的列,该列中的字符串值将被添加为文本节点。
|
comment()
|
对于名为 comment() 的列,该列中的字符串值将被添加为 XML 注释。
|
node()
|
对于名为 node() 的列,结果与列名为通配符 (*) 时相同。
|
处理指令(名称)
|
如果列名为处理指令,该列中的字符串值将被添加为此处理指令目标名称的 PI 值。
|
示例:
select 'Hui' as [first/text()],'Li' as [last/node()],'Hui Li' as [fullname/comment()],'test' as "processing-instruction(PI)" for xml path
结果:
<row>
<first>Hui</first>
<last>Li</last>
<fullname>
<!--Hui Li-->
</fullname>
<?PI test?>
</row>
带有指定为 data() 的路径的列名
如果被指定为列名的路径为 data(),则在生成的 XML 中,该值将被作为一个原子值来处理。如果序列化中的下一项也是一个原子值,则将向 XML 中添加一个空格字符。这在创建列表类型化元素值和属性值时很有用。
with T
as
(
select 11 as id
union all
select 22
union all
select 33
)
select id as [data()] from T for xml path ('')
结果:
11 22 33
这里的('')可以理解为把XML的根元素名称设为空。
NULL值列
select 'hui' as [name],null as [address] for xml path
结果:
<row>
<name>hui</name>
</row>
没有任何相关address的内容,指定ELEMENTS XSINIL后:
select 'hui' as [name],null as [address] for xml path ,ELEMENTS XSINIL
结果:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<name>hui</name>
<address xsi:nil="true" />
</row>
PATH 模式中的命名空间支持
WITH XMLNAMESPACES(N'乐可乐可的部落格' as a)
SELECT 1 as 'a:b'
FOR XML PATH
结果:
<row xmlns:a="乐可乐可的部落格">
<a:b>1</a:b>
</row>
后记:准备到这,去考了一次,竟然过了!后两大块内容占的还是不少的。
分享到:
相关推荐
Barrier-free work with ARIS Publisher Exports
data science for business
Barrier-free work with ARIS Publisher Exports Version 9.8 – Service Release 5
Python: End-to-end Data Analysis by Phuong Vothihong English | 31 May 2017 | ASIN: B072M6868D | 1321 Pages | AZW3 | 27.07 MB Leverage the power of Python to clean, scrape, analyze, and visualize your...
Lightning Web组件和Salesforce数据:使用Lightning Data Service处理数据contactCreator.html: contactCreator.js: 从“ lwc”导入{LightningElement}; 从“ lightning / platformShowToastEvent”导入{...
Energy-efficient work-stealing language runtimes 2014 asplos
You will find out how to work with the App Builder and Page Designer, use APEX themes (responsive and mobile included), templates and wizards, and design and deploy custom web apps. New and updated ...
R is one of the most popular, powerful data analytics languages and environments in use by data scientists. Actionable business data is often stored in Relational Database Management Systems (RDBMS), ...
lua-5.4.0-work1.tar
This book, Data Manipulation with R, is aimed at giving intermediate-to-advanced level users of R (who have knowledge about datasets) an opportunity to use state-of-the-art approaches in data ...
High-dimensional data can be converted to low-dimensional codes by training a multilayer neural network with a small central layer to reconstruct high-dimensional input vectors. Gradient descent can ...
Laravel开发-laravel-teamwork 用于团队项目管理API的PHP包装器
Image processing Home work with matlab code inside pdf with detail explanation
The purpose of 2017 International Conference on Security with Intelligent Computing and Big-data Services (SICBS’17 for short) with joined workshops, Workshop on Information and Communication ...
sonatype-work\nexus.rar sonatype-work\nexus.rar sonatype-work\nexus.rar sonatype-work\nexus.rar
Data Access - Retreive and Process data with a SQL Data Reader Data Access - Sort and Filter with a DataView Data Access - Use ADO 2.6 Data Access - Use Stored Procedures Data Access - Using Typed ...
Now, if you look for the last trendy technologies (Big Data, NoSQL or JavaScript), you’ll find more in-depth articles explaining how they work. Are relational databases too old and too boring to be ...
Have a custom API that you aren't sure how to use with Ember Data? Interested in writing your own adapter or serializer? Want to just know more about how Ember Data works? This is the Ember Data book ...
Designing with Data: Improving the User Experience with A/B Testing by Rochelle King English | 29 Mar. 2017 | ASIN: B06XY9TTN8 | 370 Pages | AZW3 | 4.61 MB On the surface, design practices and data ...
Work with both machine-readable and human-consumable data Scrape websites and APIs to find a bounty of useful information Clean and format data to eliminate duplicates and errors in your datasets ...