How to write `select ... FOR XML` queries that generates an xml and an xsd so, that they are kitchen ready for an SQLXMLBulkLoad?

Question

I'm looking for the most easy way to export a table (or a part of it) to an xml file and then import this xml file into a corresponding table in some other database.

The principle I found is very simple:

1. Export: on the source database I generate an xml string and also an xsd schema string by adding a FOR XML root('Data') and FOR XML, XMLSCHEMA clauses to the select query.
2. Import: on the target database I bulkload the generated xml file by SQLXMLBulkLoad using the generated xsd.

But I can't do exactly this. Between the export and the import I have to make some minor modifications in the xsd schema.

For example, I generate the xml and the xsd strings by the following queries:

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

and

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

The resulting generated.xml and generated.xsd look so:

<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>

and

<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>

But if I want to bulkload by a vb script like this

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

then this works only if I make the following modifications in the generated.xsd

• remove this xsd:schema attribute: targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"
• add this xsd:schema attribute: xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
• Replace the <myTable> element by a sequence of <myTable> elements and wrap the whole into a <xsd:element name="Data" sql:is-constant="1"> element
• Add the attributes maxOccurs="unbounded" sql:relation="myTable" to the <myTable> element

So, the modified xsd which is really suitable to bulkload the generated xml by SQLXMLBulkLoad looks like this:

<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>

I wonder if the generating sql queries and/or the vbscript can be modified so, that the generated xml and xsd work with the vbscript without any manual modification?

Answer 1

As your question has asked, the SIMPLEST way, which can be highly portable and compatible, can use simple XML data sets like so. Assuming you have two SQL servers. I have used this method between SQL>Excel, SQL>SQL, SQL>Oracle.

You can do on SQL, as a stored procedure call:

DECLARE @xml xml

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

This will return:

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

Once you have the XML, you simply read into the TARGET database using something like this:

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)

You can also run queries, etc on the incoming XML data quite easily if needed:

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%'

Very efficient and very fast. No need to mess around with schemas. Only thing is that the field types are hard coded.. so you need to custom build each of the SP read/writes.

If you have some sort of agent (e.g. MS Excel doing this), its just a simple case of writing a stored procedure to receive the XML data in the target database:

CREATE PROCEDURE sp_target_for_XML
@XML xml