Implicit queries: figure

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

Monday, March 26, 2012

import HTML table into SQL table

Wednesday, March 7, 2012

Import Complex XML file into Sql Server 2000

Implicit queries

Blog Archive

About Me