Wednesday, March 7, 2012

Import Complex XML file into Sql Server 2000

I need help importing a complex xml file using the XML Bulk Load component. I need there to be 2 tables as shown below. I just

cannot seem to figure out how to get this to work with such a complex XML structure. I have shown below my table structure, a

sample of one of the entries of the XML files and what I have so far for my XSD schema. Any help would be great!!!

My Tables:
CREATE TABLE [dbo].[WPXML] (
[Part] [varchar] (100) PRIMARY KEY,
[BaseVehicle] [int] NULL ,
[Qty] [int] NULL ,
[PartType] [int] NULL ,
[EngineBase] [int] NULL ,
[EngineDesignation] [int] NULL ,
[ImageURL] [varchar] (100) NULL ,
[ThumbURL] [varchar] (100) NULL
)
GO
CREATE TABLE [dbo].[WPPRODUCT] (
[Part] [varchar] (100) PRIMARY KEY ,
[PartNumber] [varchar] (100) NULL ,
[BrandID] [varchar] (4) NULL ,
[BrandDescription] [varchar] (100) NULL ,
[Price] [varchar] (10) COLLATE NULL ,
[ListPrice] [varchar] (10) COLLATE NULL,
[Weight] [varchar] (10) COLLATE NULL,
[Popularity] [varchar] (10) NULL,
[OEFlag] [varchar] (10) NULL,
[ProductRemark] [varchar] (1000) NULL,
[Note] [varchar] (5000) NULL
)
GO

Sample of XML:
<App action="A" id="1484266">
<BaseVehicle id= "5899"/>
<EngineBase id= "555"/>
<EngineDesignation id= "138"/>
<Qty>0</Qty>
<PartType id= "6192"/>
<Part>W0133-1621038</Part>
<Product>
<PartNumber>W0133-1621038</PartNumber>
<BrandID>FUL</BrandID>
<BrandDescription><![CDATA[Full]]></BrandDescription>
<Price>17.38</Price>
<ListPrice>36.60</ListPrice>
<Available>Y</Available>
<Weight>1.05</Weight>
<Popularity>B</Popularity>
</Product>
<Product>
<PartNumber>W0133-1611982</PartNumber>
<BrandID>KN</BrandID>
<BrandDescription><![CDATA[K&N Filters]]></BrandDescription>
<Price>68.78</Price>
<ListPrice>105.81</ListPrice>
<Available>Y</Available>
<Weight>1.80</Weight>
<Popularity>E</Popularity>
</Product>
<Product>
<PartNumber>W0133-1626304</PartNumber>
<BrandID>ND</BrandID>
<BrandDescription><![CDATA[Denso]]></BrandDescription>
<Price>22.34</Price>
<ListPrice>36.60</ListPrice>
<Available>Y</Available>
<OEFlag>OEM</OEFlag>
<Weight>1.05</Weight>
<notes>Notes For This Part</notes>
<Popularity>D</Popularity>
</Product>
<ImageURL><![CDATA[http://img.eautopartscatalog.com/live/W01331621038OES.JPG]]></ImageURL>
<ThumbURL><![CDATA[http://img.eautopartscatalog.com/live/thumb/W01331621038OES.JPG]]></ThumbURL>
</App>

My XSD Schema Thus Far:
<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="test"
parent="WPXML"
parent-key="Part"
child="WPPRODUCT"
child-key="Part" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="App" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:sequence>

<xsd:element name="Qty" type="xsd:integer" />
<xsd:element name="Part" type="xsd:string" />
<xsd:element name="BaseVehicle">
<xsd:complexType>
<xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" />
</xsd:complexType>
</xsd:element>
<xsd:element name="PartType">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="PartType" />
</xsd:complexType>
</xsd:element>
<xsd:element name="EngineBase">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" />
</xsd:complexType>
</xsd:element>
<xsd:element name="EngineDesignation">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" />
</xsd:complexType>
</xsd:element>
<xsd:element name="ImageURL" type="xsd:string" />
<xsd:element name="ThumbURL" type="xsd:string" />
<xsd:element name="Product" sql:relation="WPPRODUCT" sql:key-fields="Part" sql:relationship="test">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Part" type="xsd:string" />
<xsd:element name="PartNumber" type="xsd:string" >
</xsd:element>
<xsd:element name="BrandID" type="xsd:string" >
</xsd:element>
<xsd:element name="BrandDescription" type="xsd:string" >
</xsd:element>
<xsd:element name="Price" type="xsd:string" >
</xsd:element>
<xsd:element name="ListPrice" type="xsd:string" >
</xsd:element>
<xsd:element name="Weight" type="xsd:string" >
</xsd:element>
<xsd:element name="Popularity" type="xsd:string" >
</xsd:element>
<xsd:element name="OEFlag" type="xsd:string" >
</xsd:element>
<xsd:element name="ProductRemark" type="xsd:string" >
</xsd:element>
<xsd:element name="Note" type="xsd:string" >
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Suggestion: use a column of type text or ntext type to store your XML, this will allow you to search for a specific value.
You can use varchar or nvarchar, but FULL TEXT Seach will not work with this type of data. So, go for the first option.

Good luck.

|||

Take a look at this arcticle. If you have any more questions let us know.

http://support.microsoft.com/kb/316005

|||

Hello Dave,

Thanks for the reply! I have read the article many times :) The problem is, the first level elements I want to be in the master table (WPXML) only work if the data if formatted like <Qty>1</Qty>. I can use <xsd:element name="Qty" type="xsd:integer" /> to get the value and place it in the master table. However, if the data is formatted like <PartType id= "6192"/> the app wants me to create a relationship in order to insert the data. I don't want this data in the product table, only in the master table.

So I guess I am not sure how to get those values without having use an annotation for relationship. If you look at the 2 tables, you will see what data I need to be in each table. I have spent almost 20 hours on this and I just cannot get it to work. Any more info would be greatly appreciated!

|||

Any one have an answer to my mapping problem?

|||

Add to your element:
sql:relation="WPXML" sql:relationship="test"

so that it now looks like

<xsd:element name="PartType" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="PartType" />
</xsd:complexType>
</xsd:element>

do this for each of the elements setting the right sql:relation and sql:relationship

|||

This is the error I am getting now:

--------
Windows Script Host
--------
Script: D:\test\bhavesh.vbs
Line: 5
Char: 1
Error: Schema: the parent/child table of the relationship on 'BaseVehicle' does not match.
Code: 80004005
Source: Schema mapping

--------
OK
--------

--------
Windows Script Host
--------
Script: D:\test\bhavesh.vbs
Line: 5
Char: 1
Error: Schema: unable to load schema 'bhavesh.xsd'. An error occurred (bhavesh.xsd#/schema[1]/element[position() = 1 and @.name = 'App']/element[position() = 1 and @.name = 'Product']
Element "xsd:element" is not allowed in this context.).
Code: 80004005
Source: Schema mapping

--------
OK
--------

Here is current Schema:

<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="test"
parent="WPXML"
parent-key="Part"
child="WPPRODUCT"
child-key="Part" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="App" sql:relation="WPXML" type="PSECP100_Data">
<xsd:complexType name="PSECP100_Data">
<xsd:sequence>
<xsd:element name="Qty" type="xsd:integer" />
<xsd:element name="Part" type="xsd:string" />
<xsd:element name="BaseVehicle" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" />
</xsd:complexType>
</xsd:element>
<xsd:element name="PartType" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="PartType" />
</xsd:complexType>
</xsd:element>
<xsd:element name="EngineBase" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" />
</xsd:complexType>
</xsd:element>
<xsd:element name="EngineDesignation" sql:relation="WPXML" sql:relationship="test">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" />
</xsd:complexType>
</xsd:element>
<xsd:element name="ImageURL" type="xsd:string" />
<xsd:element name="ThumbURL" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="Product" sql:relation="WPPRODUCT" sql:relationship="test" type="bhavesh" >
<xsd:complexType name="bhavesh">
<xsd:sequence>
<xsd:element name="Part" type="xsd:string" />
<xsd:element name="PartNumber" type="xsd:string" >
</xsd:element>
<xsd:element name="BrandID" type="xsd:string" >
</xsd:element>
<xsd:element name="BrandDescription" type="xsd:string" >
</xsd:element>
<xsd:element name="Price" type="xsd:string" >
</xsd:element>
<xsd:element name="ListPrice" type="xsd:string" >
</xsd:element>
<xsd:element name="Weight" type="xsd:string" >
</xsd:element>
<xsd:element name="Popularity" type="xsd:string" >
</xsd:element>
<xsd:element name="OEFlag" type="xsd:string" >
</xsd:element>
<xsd:element name="ProductRemark" type="xsd:string" >
</xsd:element>
<xsd:element name="Note" type="xsd:string" >
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:element>
</xsd:schema

|||

remove sql:relationship="test" from basevehicle.

If the error moves to the next node then we know that was causing the issue.

|||

same error:

Script: D:\test\bhavesh.vbs
Line: 5
Char: 1
Error: Schema: unable to load schema 'bhavesh.xsd'. An error occurred (bhavesh.xsd#/schema[1]/element[position() = 1 and @.name = 'App']/element[position() = 1 and @.name = 'Product']
Element "xsd:element" is not allowed in this context.).
Code: 80004005
Source: Schema mapping

I am close to giving up on this!!!

|||

Okay I took your schema and tried to validate it with xmlspy (http://www.altova.com) and it didn't validate.

Give this schema a try and see how it works out. 

<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="test" parent="WPXML" parent-key="Part" child="WPPRODUCT" child-key="Part" /></xsd:appinfo></xsd:annotation><xsd:element name="App" sql:relation="WPXML"><xsd:complexType><xsd:sequence> <xsd:element name="Qty" type="xsd:integer" /><xsd:element name="Part" type="xsd:string" /><xsd:element name="BaseVehicle" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType><xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" /> </xsd:complexType></xsd:element><xsd:element name="PartType" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType><xsd:attribute name="id" type="xsd:integer" sql:field="PartType" /></xsd:complexType></xsd:element><xsd:element name="EngineBase" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType><xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" /></xsd:complexType></xsd:element><xsd:element name="EngineDesignation" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType><xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" /></xsd:complexType></xsd:element><xsd:element name="ImageURL" type="xsd:string" /><xsd:element name="ThumbURL" type="xsd:string" /></xsd:sequence></xsd:complexType></xsd:element> <xsd:element name="Product" sql:relation="WPPRODUCT" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="PartNumber" type="xsd:string" > </xsd:element> <xsd:element name="BrandID" type="xsd:string" > </xsd:element> <xsd:element name="BrandDescription" type="xsd:string" > </xsd:element> <xsd:element name="Price" type="xsd:string" > </xsd:element> <xsd:element name="ListPrice" type="xsd:string" > </xsd:element> <xsd:element name="Weight" type="xsd:string" > </xsd:element> <xsd:element name="Popularity" type="xsd:string" > </xsd:element> <xsd:element name="OEFlag" type="xsd:string" > </xsd:element> <xsd:element name="ProductRemark" type="xsd:string" > </xsd:element> <xsd:element name="Note" type="xsd:string" > </xsd:element> </xsd:sequence> </xsd:complexType></xsd:element></xsd:schema>
|||

Hello Dave, almost there! I really appreciate your assistance on this :)

Here is my problem... everything is working perfect with the schema below except I cannot seem to get the values for any element when the value is an attribute of the element. You will see the element <BaseVehicle id="5899" /> in the example of the XML in my original post, it seems my schema would work if the data was formatted like <BaseVehicle>5899</Basevehicle> any ideas on how to get those values without having to store the value in both tables?

Schema:

<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="PartNumbers"
parent="WPXML"
parent-key="Part"
child="WPPRODUCT"
child-key="Part" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="App" sql:relation="WPXML" type="b"/>
<xsd:complexType name="b">
<xsd:sequence>
<xsd:element name="Qty" type="xsd:integer" sql:field="Qty"/>
<xsd:element name="BaseVehicle" sql:field="BaseVehicle" type="xsd:integer"/>
<xsd:element name="PartType" sql:field="PartType" type="xsd:integer"/>
<xsd:element name="EngineBase" sql:field="EngineBase" type="xsd:integer"/>
<xsd:element name="EngineDesignation" sql:field="EngineDesignation" type="xsd:integer"/>
<xsd:element name="ImageURL" type="xsd:string"/>
<xsd:element name="ThumbURL" type="xsd:string"/>
<xsd:element name="Note" type="xsd:string" sql:field="Note"/>
<xsd:element name="Part" type="xsd:string" sql:field="Part"/>
<xsd:element ref="Product" sql:relationship="PartNumbers" maxOccurs="unbounded" />
</xsd:sequence>
<xsd:attribute name="id" sql:field="PartType" type="xsd:integer" />
</xsd:complexType>
<xsd:element name="Product" sql:relation="WPPRODUCT" type="bhavesh" />
<xsd:complexType name="bhavesh">
<xsd:sequence>
<xsd:element name="Part" type="xsd:string" sql:field="Part"/>
<xsd:element name="PartNumber" type="xsd:string" sql:field="PartNumber"/>
<xsd:element name="BrandID" type="xsd:string" sql:field="BrandID"/>
<xsd:element name="BrandDescription" type="xsd:string" sql:field="BrandDescription"/>
<xsd:element name="Price" type="xsd:string" sql:field="Price"/>
<xsd:element name="ListPrice" type="xsd:string" sql:field="ListPrice"/>
<xsd:element name="Weight" type="xsd:string" sql:field="Weight"/>
<xsd:element name="Popularity" type="xsd:string" sql:field="Popularity"/>
<xsd:element name="OEFlag" type="xsd:string" sql:field="OEFlag"/>
<xsd:element name="ProductRemark" type="xsd:string" sql:field="ProductRemark"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>

|||

only part that looks to be missing is your sql:relation for each of the xsd:element and xsd:attribute. That will it which table / field it belongs with. Otherwise its going to want to put in both.

|||

I have changed the schema to the below. The problem is, because I changed the basevehicle elament to:
<xsd:element name='BaseVehicle' sql:relation="WPXML">
<xsd:complexType>
<xsd:attribute name='id' sql:field="BaseVehicle" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>

It now wants me to add a relationship, but I don't want these values in both tables, I only want them in the first. Do I have to store the attribute data in 2 tables or is there a way to get the data without adding it to both?

schema:

<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="PartNumbers" parent="WPXML" parent-key="Part" child="WPPRODUCT" child-key="Part" />
</xsd:appinfo>
</xsd:annotation>

<xsd:element name="App" sql:relation="WPXML" type="b"/>
<xsd:complexType name="b">
<xsd:sequence>
<xsd:element name="Qty" type="xsd:integer" sql:field="Qty"/>
<xsd:element name='BaseVehicle' sql:relation="WPXML">
<xsd:complexType>
<xsd:attribute name='id' sql:field="BaseVehicle" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="PartType" sql:field="PartType" type="xsd:integer"/>
<xsd:element name="EngineBase" sql:field="EngineBase" type="xsd:integer"/>
<xsd:element name="EngineDesignation" sql:field="EngineDesignation" type="xsd:integer"/>
<xsd:element name="ImageURL" type="xsd:string"/>
<xsd:element name="ThumbURL" type="xsd:string"/>
<xsd:element name="Note" type="xsd:string" sql:field="Note"/>
<xsd:element name="Part" type="xsd:string" sql:field="Part"/>
<xsd:element ref="Product" sql:relationship="PartNumbers" maxOccurs="unbounded" />
</xsd:sequence>
</xsd:complexType>

<xsd:element name="Product" sql:relation="WPPRODUCT" type="bhavesh" />
<xsd:complexType name="bhavesh">
<xsd:sequence>
<xsd:element name="Part" type="xsd:string" sql:field="Part"/>
<xsd:element name="PartNumber" type="xsd:string" sql:field="PartNumber"/>
<xsd:element name="BrandID" type="xsd:string" sql:field="BrandID"/>
<xsd:element name="BrandDescription" type="xsd:string" sql:field="BrandDescription"/>
<xsd:element name="Price" type="xsd:string" sql:field="Price"/>
<xsd:element name="ListPrice" type="xsd:string" sql:field="ListPrice"/>
<xsd:element name="Weight" type="xsd:string" sql:field="Weight"/>
<xsd:element name="Popularity" type="xsd:string" sql:field="Popularity"/>
<xsd:element name="OEFlag" type="xsd:string" sql:field="OEFlag"/>
<xsd:element name="ProductRemark" type="xsd:string" sql:field="ProductRemark"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>

|||

Where does it asking you to add the relationship. I see you have the ref element on the Part branch, but I'm not seeing the relationship added to the product branch.

|||

Hi,

Does the bulkload works for xml to sql no matter if itshierarchical xml?

because I'm trying to do my schema file too

Anyone knows a good tutorial for making schemas?

Thanks in advance!

Posted by feltpaddedvbny at 8:36 AM
Labels: , , , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)