从 SQL Server 2005 中处理 XML

2024-08-31 00:50:12

字体：大中小

来源：转载

供稿：网友

收集最实用的网页特效代码！

　　一、简介

　　microsoft sql server 2005 中的重大更改之一是包含了 xml 数据类型。此数据类型是第一类类型，就像 int 或 varchar 一样，而且 sql server 2005 允许使用一系列 xml 特定的函数对此数据类型进行就地查询和处理。它还支持存储数据库中的 xml 架构的集合，从而启用基于数据库的架构验证。另外，sql server 2005 大大地扩展了 xml 组合（select ... for xml 语句）的功能，扩展了 openxml() xml 分解函数，并针对 xml 数据类型提供了一个新的 nodes() 函数以进行更轻量级的分解。

　　既然对数据库服务器增强了此新的 xml 功能，那么对 microsoft ado.net 2.0 中的 sqlclient 数据提供程序也进行增强就不会令人感到惊奇了。对 ado.net dataset 也有更改，以便支持类型 xml 的 datacolumn，而且 system.data 和 system.xml 之间的“集成点”得到了拓宽。在本文中，我将探究在客户端上使用 sql server 2005 xml 数据类型。

　　sql server 2005 可以产生两类 xml 输出。语句 select * from authors for xml auto 产生 xml 流，而不是一列一行的行集。该输出类型与 sql server 2000 中的输出类型相比没有改变。只是因为查询分析器工具中的限制，xml 流输出在 sql server 查询分析器中才显示为一列一行的行集。您可以通过其特定的唯一标识符名称“xml_f52e2b61-18a1-11d1-b105-000805f49916b”来将这种流与“普通”列区分开来。此名称实际上是底层 tds（这是一种表格式的数据流，sql server 网络格式）分析器的指示器，在这种分析器中，列应该流至客户端，而不是像普通行集那样发送。有一种特殊的方法 sqlcommand.executexmlreader 用来在客户端上检索此特殊的流。在 sql server 2005 中，select ... for xml 语句通过许多方式得到了增强。这里仅提少数几种：

|||，欢迎访问网页设计爱好者web开发。

　　在大多数情况下，当您需要 sql server 2000 中的 for xml explicit 模式时，有一种新的、便于使用的 for xml path 模式。

　　使用 type 指令，除了生成流之外，您还可以生成 xml 数据类型列。

　　可以嵌套 for xml 表达式。

　　select ... for xml 可以使用 root 指令生成 xml 文档以及 xml 片段。

　　您可以将标准的 xsd 架构预先挂起到流。

　　通过引用 ado.net 2.0 中的关系 datatype 枚举，您可以初步了解到 xml 是一种一流的关系数据库类型。system.data.dbtype 和 system.data.sqldbtype 分别包含 dbtype.xml 和 sqldbtype.xml 的附加值。在 system.data.sqltypes 命名空间中也有一个新的类，它是 sqlxml。这个类充当 xml 类型值的 xmlreader 实例工厂。我将通过一些简单的代码进行展示。假设我有一份 sql server 表，如下所示：

create table xmltab (
　id int identity primary key,
　xmlcol xml)

　　我可以使用以下 ado.net 2.0 代码在客户端上访问此表。

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void getxmlcolumn {
// "generic coding..." article for shows how to
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select * from xmltab", conn))
{
　conn.open();
　sqldatareader rdr = cmd.executereader();
　datatable t = rdr.getschematable();
　while (rdr.read())
　　{
　　　sqlxml sx = rdr.getsqlxml(1);
　　　xmlreader xr = sx.createreader();
　　　xr.read();
　　　console.writeline(xr.readouterxml());
　　}
}
}

　　我浏览 getschematable 产生的 datatable 时返回的列元数据正确地标识了列：

|||

providertype: 25 (25 = xml)
providerspecificdatatype: system.data.sqltypes.sqlxml
datatype: system.xml.xmlreader
datatypename:

　　正如任何其他构建到 sql server 中的类型一样。请注意，此列的“.net 类型”是 xmlreader，对于 .net 而言，它就像任何从文件加载或用 xmldocument 类产生的 xml 一样。在 ado.net 2.0 中的存储过程或参数化语句中将 xml 数据类型列用作参数同样简单：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void addarow {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "insert xmltab(xmlcol) values(@x)", conn))
{
　conn.open();
　cmd.parameters.add("@x", sqldbtype.xml);
　// connect the parameter value to a file
　xmlreader xr = xmlreader.create("somexml.xml");
　cmd.parameters[0].value = new sqlxml(xr);
　int i = cmd.executenonquery();
}
}

　　二、是 xml 还是字符串？

　　前面代码中的两种方法均使用 sqltypes 中 sql server 特定的数据类型。当我使用 sqlreader 的更一般的访问器方法 getvalue() 时，值显著不同。列不是作为 xmlreader 出现，而是作为 .net string 类出现。请注意，即使元数据将列的 .net 数据类型识别为 xmlreader，您也不能将该列强制转换为 xmlreader。使用除 getsqlxml() 之外任何访问器都返回字符串。

|||

-- t-sql stored procedure definition
create procedure insert_xml(@x xml)
as
insert xmltab(xmlcol) value(@x)
// client-side code
using system;
using system.data;
using system.data.sqlclient;
void insertxmlfromclient {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd1 = new sqlcommand(
　　　 "insert xmltab(xmlcol) values(@x)", conn))
using (sqlcommand cmd2 = new sqlcommand(
　　　 " insert_xml", conn))
{
　string str = "<somedoc/>";
　conn.open();
　
　// server-side conversion
　cmd1.parameters.add("@x", sqldbtype.nvarchar);
　cmd1.parameters[0].value = str;
　cmd1.executenonquery();
　// client-side conversion works too
　cmd2.commandtype = commandtype.storedprocedure;
　cmd2.parameters.add("@x", sqldbtype.xml);
　cmd2.parameters[0].value = s;
　cmd2.executenonquery();
}
}
三、文档、片段和 for xml 支持
sql server 2005 中的 xml 数据类型既支持 xml 文档，也支持 xml 文档片段。片段与文档的区别在于，片段能包含多个顶级元素和空文本节点。对于类型化的 xml 列/变量/参数，您可以使用 document（不允许片段）或 content（允许片段）规范指定是否允许片段。content 是默认值，非类型化的 xml 允许片段。以下 t-sql 代码举例说明了对片段的支持：
create table xmltab (
　id int identity primary key,
　xmlcol xml)
go
-- insert a document
insert xmltab values('<doc/>')
-- fragment, multiple top-level elements
insert xmltab values('<doc/><doc/>')
-- fragment, bare text node
insert xmltab values('hello world')
-- even this fragment works
insert xmltab values('<doc/>sometext')

　　select ... for xml 也会生成 xml 片段。语句 select job_id, min_lvl, max_lvl from jobs for xml auto 生成以下输出。请注意，有多个根元素。

|||国内最大的酷站演示中心!

<jobs job_id="1" min_lvl="10" max_lvl="10" />
<jobs job_id="2" min_lvl="200" max_lvl="250" />
<jobs job_id="3" min_lvl="175" max_lvl="225" />
<jobs job_id="4" min_lvl="175" max_lvl="250" />
<!-- some jobs rows deleted for compactness -->

　　使用 sqlxml 既支持文档，也支持片段。sqlxmlcreatereader() 方法总是创建一个 xmlreader，它通过使用新的 xmlreadersettings 类来支持片段，如下所示：

// pseudocode from sqlxml.createreader
　　stream stm = stm; // stream filled from column (code elided)
　　xmlreadersettings settings = new xmlreadersettings();
　　settings.conformancelevel = conformancelevel.fragment;
　　xmlreader xr = xmlreader.create(
　　　 stm, string.empty, null, null,settings);

　　如果您以同样的方式构造 xmlreader，则可以在输入参数中使用 xml 片段。尽管使用 sqlxml 类型时内置了片段支持，但是您在处理包含片段的 xmlreader 时仍需要小心。请注意，调用 xmlreader.getouterxml() 将只提供第一个片段；要定位 xmlreader 以获取随后的片段，您必须再次调用 xmlreaderread 方法。我将在本文的稍后部分说明此问题。

　　t-sql 的“select ... for xml”生成了 xml 流，而不是一列一行的行集。它还以二进制格式而非 xml 的标准序列化提供了 xml。由于格式的不同，以及“select ... for xml”总是生成片段，因此使用它需要特殊的方法。出于此目的，sqlclient 实现了一种提供程序特定的方法 sqlcommand.executexmlreader。如果使用 sql server 2000 和 ado 1.0/1.1，则需要使用 executexmlreader 以获取 for xml 查询的结果，除非您想使用某些需要字符串连接的相当不好的替代方案。通过 sql server 2005 for xml 增强功能以及对 xml 作为数据类型的支持，您只需使用 executexmlreader 就能从 sql server 获取单个 xml 流。由于所有为 sql server 2000 编写的代码都使用此方法，因此该方法在 ado.net 2.0 中也受支持，而且得到了增强。

|||

　　您可以像在以前的版本中一样，使用 executexmlreader 从“for xml”查询中检索任何流。另外，此方法支持对普通 select 语句生成的 xml 数据类型列进行检索。这里唯一需要提请您注意的是，当普通 select 语句返回多行时，executexmlreader 仅返回第一行的内容。这里的示例使用与先前示例中相同的表展示了这一点：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void useexecxmlreader {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd1 = new sqlcommand(
　　　 "select * from pubs..authors for xml auto,root('root')", conn))
using (sqlcommand cmd2 = new sqlcommand(
　　　 "select * from pubs..authors for xml auto", conn))
using (sqlcommand cmd3 = new sqlcommand(
　　　 "select * from xmltab", conn))
{
　conn.open();
　// contains document
　xmlreader xr1 = cmd1.executexmlreader();
　// contains fragment
　xmlreader xr2 = cmd2.executexmlreader();
　// contains contents of first row in xmltab only
　xmlreader xr3 = cmd3.executexmlreader();
　// use xmlreaders, then
　xr1.dispose(); xr2.dispose(); xr3.dispose();
}
}

　　为了完成关于在 ado.net 2.0 中获取 xml 的讨论，在各种使用方案中提到 xmlreader 内容的生命周期是有好处的。研究 xmlreader 的生命周期还有助于理解 sqlclient 执行的缓冲，以及如何使用此数据获取最高性能。xmlreader 使用资源，为了释放这些资源，应该调用 close() 或 dispose() 方法，就像使用 sqlconnection、sqlcommand 和 sqldatareader 一样。在通过 sqldatareader 读取 xml 列的情况下，可以为每一行分配一个 xmlreader。请记住，为了支持在同一行的列中向后移动，或者移动到下一行，xmlreader 的内容要在内存中缓冲。当您使用 sqlcommand 的 commandbehavior.sequentialaccess 时，整个 xmlreader 不会在内存中缓冲，但您使用此存取方法时必须小心。使用 commandbehavior.sequentialaccess 时，与列相关联的 xmlreader 在行集中移动到下一列之前必须完全消耗掉；在移动到下一列之后，xmlreader 看似有效，但调用它的 read() 方法不会产生任何数据。当您使用 executexmlreader 或 executescalar 代替 executereader 时，您无需同样多的了解此行为，但此时也别忘了关闭/处理 xmlreader。

|||

　　四、在客户端上使用 xml 架构支持

　　sql server 2005 支持强类型化的 xml，这意味着 xml 必须符合某个 xml 架构或 xml 架构集。通过使用 sql server 中的架构集合可以实现这种支持。xml 架构集合如同任何其他 sql server 对象一样定义，xml 架构存储在 sql server 中。t-sql ddl create 语句和 xml 架构集合的用法如下所示：

create xml schema collection books_xsd
as
-- one or more xml schemas here
go
create table typed_xml (
　id int identity primary key,
　-- require books_col content to be schema-valid
　books_col xml(books_xsd)
)
-- validated here
insert typed_xml values('<!-- some document -->')
-- validated here too
update typed_xml
　set books_col.modify('<!-- some xquery dml -->')
　where id = 1

　　当您从客户端使用 sql server 2005 中强类型化的 xml 数据时，验证是在服务器上执行，而非在客户端上执行。例如，如果您使用前述示例中显示的 addarow 方法向 typed_xml 表添加一行，则在验证发生之前，数据跨网络发送到 sql server。然而，只需少量工作，就可以从sql server xml schema collections 检索 xml 架构，并将它们隐藏在客户端上以完成客户端的验证。这可以防止用户或 web 服务将架构无效的 xml 发送到用于 sql server 存储的客户端，从而节省了一些反复的操作，但是，必须考虑两处警告/澄清。首先，依赖于从 sql server 所获取的 xml 架构信息如同依赖于任何高速缓存的客户端元数据。可能某人已使用 t-sql alter xml schema 语句改变了架构集合，或者甚至删除了架构集合并重新创建它，从而使得您的客户端的检查无用。您可以在 create/alter/drop xml schema ddl 语句上使用新的 event notification，以防止出现异常。然后，您将使用自定义代码监视 service broker service，这类似于使用带有查询通知的 sqlnotificationrequest。event notifications 是 sql server 2005 中的新功能，但它类似于在我前面的文章 ado.net 2.0 中的查询通知中讨论的查询通知。第二，请记住，即使您在客户端执行 xml 架构验证，sql server 也将在服务器上重新检查。没有办法向 sql server 指示“我知道这个 sql server 架构类型化的 xml 实例的架构有效，请不要麻烦地自己检查了。”

|||

　　要进行客户端的 xml 架构验证，您可以使用 t-sql 函数 xml_schema_namespace() 检索 sql server xml schema collection。这需要 xml 架构集合名称和数据库架构名称，因为 xml 架构集合包括数据库架构。我们可以将它们硬编码到程序中，或者从客户端行集元数据提取它们。通过使用上面的 sqldatareadergetschematable 方法或使用新的 sqlmetadata 类，获取与一个特定列相关联的架构集合的名称是很容易的。这里有一小段代码示例：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sql;
void getcollectioninfo {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select books_col from typed_xml", conn))
{
　conn.open();
　// fetch only sql server metadata
　sqldatareader rdr = cmd.executereader(commandbehavior.schemaonly);
　sqlmetadata md = rdr.getsqlmetadata(0);
　string database = md.xmlschemacollectiondatabase;
　string schema = md.xmlschemacollectionowningschema;
　string collection = md.xmlschemacollectionname;
}
}

　　一旦您了解要检索哪个 xml 架构集合，就可以使用 t-sql 函数将其检索到客户端 xmlschemaset 中。请注意，此示例还展示了如何使用 xmlreader 检索片段。这是必需的，因为 xml schema collection 中的 xml 架构可能不止一个；xml_schema_namespace 函数会将集合中的所有 xml 架构作为一个片段返回。

|||

商业源码热门下载www.html.org.cn

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
using system.xml.schema;
void getschemaset {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select xml_schema_namespace(n'dbo',n'books_xsd')", conn))
{
　xmlschemaset ss = new xmlschemaset();
　conn.open();
　sqldatareader rdr = cmd.executereader();
　rdr.read();
　xmlreader xr = rdr.getsqlxml(0).createreader();
　do
　{
　　ss.add(xmlschema.read(xr, null));
　　xr.read();
　}
　while (xr.nodetype == xmlnodetype.element);
}
}

　　如果手边有 xmlschemaset，我们就可以将客户端 xml 验证代码集成到 add 例程中。您瞧！这就是客户端验证。

void validateandstore(xmlschemaset ss)
{
　// associate the xmlschemaset with the xmlreader
　xmlreadersettings settings = new xmlreadersettings();
　settings.schemas = ss;
　string s = getconnectstringfromconfigfile("xmldb");
　using (xmlreader xr = xmlreader.create(
　　"file://c:/temp/somefile.xml", settings))
　// get a connection string from a config file
　using (sqlconnection conn = new sqlconnection(s))
　using (sqlcommand cmd = new sqlcommand(
　　"insert typed_xml values(@x)", conn))
　{
　　try
　　{
　　　conn.open();
　　　// should throw an exception here if not schema valid
　　　cmd.parameters.addwithvalue("@x", new sqlxml(xr));
　　　int i = cmd.executenonquery();
　　}
　　catch (exception e)
　　{
　　　 console.writeline(e.message);
　　}
　}
}