我正在寻找最简单的方法将表(或其中的一部分)导出到 xml 文件，然后将此 xml 文件导入其他数据库中的相应表。

我查到的原理很简单:

1. 导出: 在源数据库上，我通过添加 FOR XML root('Data') 生成一个 xml 字符串和一个 xsd 架构字符串和 FOR XML, XMLSCHEMA选择查询的子句。
2. 导入: 在目标数据库上，我使用生成的 xsd 通过 SQLXMLBulkLoad 批量加载生成的 xml 文件。

但我不能完全做到这一点。在导出和导入之间，我必须对 xsd 模式进行一些小的修改。

例如，我通过以下查询生成 xml 和 xsd 字符串:

select top 3 * FROM myTable
FOR XML AUTO, ELEMENTS 
,Root('Data')

和

SELECT top 0 * FROM myTable
FOR XML AUTO, ELEMENTS 
,XMLSCHEMA

生成的 generated.xml 和 generated.xsd 看起来是这样的:

<Data>
  <myTable>
      <field1>value11</field1>
      ...
      <field1>value1n</field1>
  </myTable>
  <myTable>
      <field1>value21</field1>
      ...
      <field1>value2n</field1>
  </myTable>
  <myTable>
      <field1>value31</field1>
      ...
      <field1>value3n</field1>
  </myTable>
</Data>

和

<xsd:schema 
targetNamespace="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:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
    <xsd:element name="myTable">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="field1" type="..." .../>
                ...
                <xsd:element name="fieldn" type="..." ... />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

但是如果我想通过这样的 vb 脚本批量加载

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=localhost\SQLEXPRESS;database=Testdb;uid=sa;pwd=*****"
objBL.ErrorLogFile = ".\error.xml"
objBL.KeepIdentity = False
objBL.Execute "generated.xsd", "generated.xml"
set objBL=Nothing

只有当我在 generated.xsd 中进行以下修改时，这才有效

• 删除这个xsd:schema属性:targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"
• 添加这个xsd:schema属性:xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
• 替换 <myTable>元素由 <myTable> 的序列组成元素并将整体包装成一个<xsd:element name="Data" sql:is-constant="1">元素
• 添加属性maxOccurs="unbounded" sql:relation="myTable"到 <myTable>元素

因此，真正适合通过SQLXMLBulkLoad 批量加载生成的xml 的修改后的xsd 如下所示:

<xsd:schema 
xmlns:sql="urn:schemas-microsoft-com:mapping-schema" 
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:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
    <xsd:element name="Data" sql:is-constant="1">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="myTable" maxOccurs="unbounded" sql:relation="myTable">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="field1" type="..." .../>
                            ...
                            <xsd:element name="fieldn" type="..." ... />
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

我想知道是否可以修改生成的 sql 查询和/或 vbscript，以便生成的 xml 和 xsd 无需任何手动修改即可与 vbscript 一起工作？

最佳答案

正如您的问题所问，具有高度可移植性和兼容性的最简单方法可以像这样使用简单的 XML 数据集。假设您有两个 SQL 服务器。 SQL>Excel、SQL>SQL、SQL>Oracle之间我都用过这种方法。

您可以在 SQL 上执行，作为存储过程调用:

DECLARE @xml xml

SET @XML = (
SELECT field1, field2
FROM table
FOR XML RAW('row'), ROOT('data'), ELEMENTS
)

这将返回:

<root>
  <row>
<field1>SomeData</field1>
<field2>SomeOtherData</field2>
</row>
</root>

获得 XML 后，您只需使用如下内容读入 TARGET 数据库:

INSERT INTO TargetDatabase.TargetTable(field1, field2)
SELECT tbl.c.value('field1','varchar(1000)'), tbl.c.value('field2','bigint')
FROM @XML.nodes('/root[1]/row) tbl(c)

如果需要，您还可以很容易地对传入的 XML 数据运行查询等操作:

INSERT INTO TargetTable(field1, field2)
SELECT tbl.c.value('field1','varchar(1000)'), tbl.c.value('field2','bigint')
FROM @XML.nodes('/root[1]/row) tbl(c)
WHERE tbl.c.value('field2','bigint') > 100 or tbl.c.value('field1','varchar(1000)') Like '%fish%'

效率很高，速度也很快。无需摆弄模式。唯一的问题是字段类型是硬编码的。因此您需要自定义构建每个 SP 读/写。

如果您有某种代理(例如 MS Excel 执行此操作)，这只是编写存储过程以接收目标数据库中的 XML 数据的简单案例:

CREATE PROCEDURE sp_target_for_XML
@XML xml

关于sql-server - 如何编写生成 xml 和 xsd 的 `select ... FOR XML` 查询，以便它们为 SQLXMLBulkLoad 做好准备？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42161262/