XSD for multi table load
-
Hi
I'd like to load an XML file, which contains 2 entities, into 2 respective database tables representing those entities
The 2 entities are tbl_customer and, nested under tbl_customer, tbl_address
My XML file looks as follows
<tbl_customer>
<customer_id>1093</customer_id>
<first_name>Richard</first_name>
<surname>Stubbs</surname>
<customer_type>1</customer_type>
<customer_export>0</customer_export>
<tbl_address>
<address_id>1019</address_id>
<address_1>102 The Road</address_1>
<address_2>Brentford</address_2>
<town>London</town>
<county>London SE</county>
<post_code>PP1 5GG</post_code>
<address_type>1</Addresstype>
</tbl_address>
<tbl_address>
<address_id>1020</address_id>
<address_1>101 The Road</address_1>
<address_2>Brentford</address_2>
<town>London</town>
<county>London SE</county>
<post_code>PP1 5GG</post_code>
<address_type>1</Addresstype>
</tbl_address>
</tbl_customer>
I've been trying to make sense of creating an XSD file to use an XML bulk load, but I'm a little lost
Could somebody give me some pointers
Cheers
Simon
-
your xml is malformed on the Addresstype, guess that was a typo.
------------
declare
@idoc int
declare
@doc varchar(1000)
set
@doc ='
<tbl_customer>
<customer_id>1093</customer_id>
<first_name>Richard</first_name>
<surname>Stubbs</surname>
<customer_type>1</customer_type>
<customer_export>0</customer_export>
<tbl_address>
<address_id>1019</address_id>
<address_1>102 The Road</address_1>
<address_2>Brentford</address_2>
<town>London</town>
<county>London SE</county>
<post_code>PP1 5GG</post_code>
<address_type>1</address_type>
</tbl_address>
<tbl_address>
<address_id>1020</address_id>
<address_1>101 The Road</address_1>
<address_2>Brentford</address_2>
<town>London</town>
<county>London SE</county>
<post_code>PP1 5GG</post_code>
<address_type>1</address_type>
</tbl_address>
</tbl_customer>'
--Create an internal representation of the XML document.
exec
sp_xml_preparedocument @idoc OUTPUT, @doc
--get the customer details
SELECT
*
FROM
OPENXML (@idoc, '/tbl_customer',1)
WITH (customer_id varchar(10) 'customer_id',
first_name varchar
(20) 'first_name')
--get the address
SELECT
*
FROM
OPENXML (@idoc, '/tbl_customer/tbl_address',1)
WITH (customer_id varchar(10) '../customer_id',
address_id varchar
(10) 'address_id',
address_1 varchar
(20) 'address_1')
-------
refer to BOL on the usage of OPENXML
-
Hi Bledu,
Many thanks for that, I was originally looking at the openXML option, but I've gone down the XML Bulkload option
I've create two tables called tbl_impcustomer and tbl_impcustomeraddress in which to load my XML. I was a little mystified originally about setting the relationship tags for mutlitable loads, but through a bit of muddling through have come up with the following XSD which has now worked successfully.
I should add that I have now capitalised some of the tags in my XML file and fixed the type that you mentioned 😉 such that the following XSD works
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustomerCustomerAddress"
parent="tbl_impCustomer"
parent-key="Customer_ID"
child="tbl_impCustomerAddress"
child-key="Customer_ID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="tbl_customer" sql:relation="tbl_impCustomer" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customer_ID" type="xsd:integer" />
<xsd:element name="Email" type="xsd:string" />
<xsd:element name="First_Name" type="xsd:string" />
<xsd:element name="Surname" type="xsd:string" />
<xsd:element name="Customer_Type" type="xsd:integer" />
<xsd:element name="Customer_Export" type="xsd:integer" />
<xsd:element name="tbl_address"
sql:relation="tbl_impCustomerAddress"
sql:relationship="CustomerCustomerAddress" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Address_ID" type="xsd:integer" />
<xsd:element name="Address_1" type="xsd:string" />
<xsd:element name="Address_2" type="xsd:string" />
<xsd:element name="Town" type="xsd:string" />
<xsd:element name="County" type="xsd:string" />
<xsd:element name="Post_Code" type="xsd:string" />
<xsd:element name="Address_Type" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
-
After successfully using my SQLXML import now, I have now noted that on the SQLXMLBulkUpload class there are the extraordinarily useful properties
Schemagen and
SGDroptables
This looks handy I thought to myself and with the addition of a sql:key-fields attribute executing with the schemagen=true, the following schema not only knows how to create my tables but also how to define the primary keys and the foreign key constraints in SQL Server
Unfortunately setting SGDroptables = true results in the SQLXML object trying to delete the tbl_impcustomer table before its dependant tbl_impcustomeraddress the error "Could not drop table tbl_impcustomer as it is referenced by a foreign key constraint
If the schema has enough info to create the foreign key constraint with Schemagen, why hasn't it got enough to account for the dependcies and drop the tables in the order that will not violate the constraint. Is there something that I've neglected ?
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustomerCustomerAddress" parent="tbl_impCustomer" parent-key="Customer_ID" child="tbl_impCustomerAddress" child-key="Customer_ID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="tbl_customer" sql:relation="tbl_impCustomer" sql:key-fields="Customer_ID" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customer_ID" type="xsd:integer" sql:datatype="int" />
<xsd:element name="Email" type="xsd:string" sql:datatype="varchar(53)" />
<xsd:element name="First_Name" type="xsd:string" sql:datatype="varchar(40)" />
<xsd:element name="Surname" type="xsd:string" sql:datatype="varchar(40)" />
<xsd:element name="Customer_Type" type="xsd:integer" sql:datatype="int" />
<xsd:element name="Customer_Export" type="xsd:integer" sql:datatype="int" />
<xsd:element name="tbl_address" sql:relation="tbl_impCustomerAddress" sql:relationship="CustomerCustomerAddress" sql:key-fields="Address_ID" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Address_ID" type="xsd:integer" sql:datatype="int" />
<xsd:element name="Address_1" type="xsd:string" sql:datatype="varchar(80)" />
<xsd:element name="Address_2" type="xsd:string" sql:datatype="varchar(80)" />
<xsd:element name="Town" type="xsd:string" sql:datatype="varchar(40)" />
<xsd:element name="County" type="xsd:string" sql:datatype="varchar(40)" />
<xsd:element name="Post_Code" type="xsd:string" sql:datatype="varchar(12)" />
<xsd:element name="Address_Type" type="xsd:integer" sql:datatype="int" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply