|
| Hi
Not been using SQLXML3 or XML for very long.
Having difficulties with the following xml file and its xsd.
I want to import the XML file into SQL Server 2000 using SQLXML3 and I am
using the xsd to map the elements and attributes in the xml file to a table
on my server
Everything is fine except where I have a nested element in the xml file
which contains attributes that need mapping to fields in my table, I receive
an error complaning that the element is not setup with a relation to a column
in my table.
The error is Schema: relationship expected on 'picture'. Specifying a
relation to the table for this element displays the same error message.
I have attached the table structure and the xml and xsd files below
Table script....
CREATE TABLE [dbo].[MyTable] (
[RowId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ArticleId] [varchar] (20) NULL ,
[Created] [varchar] (50) NULL ,
[Heading] [varchar] (300) NULL ,
[LongHeading] [varchar] (500) NULL ,
[Date] [smalldatetime] NULL ,
[Contents] [text] NULL ,
[Summary] [varchar] (500) NULL ,
[Priority] [varchar] (20) NULL ,
[PicOrientation] [varchar] (10) NULL ,
[PicPhotoTag] [varchar] (100) NULL ,
[Ratio] [float] NULL ,
[PhotoId] [varchar] (20) NULL ,
[PhotoTag] [varchar] (50) NULL ,
[LargeWidth] [smallint] NULL ,
[LargeHeight] [smallint] NULL ,
[LargeURL] [varchar] (255) NULL ,
[SmallWidth] [smallint] NULL ,
[SmallHeight] [smallint] NULL ,
[SmallURL] [varchar] (255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
xsd file....
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="Article" sql:relation="MyTable">
<xs:complexType>
<xs:sequence>
<xs:element name="Heading" type="xs:string" sql:field="Heading" />
<xs:element name="LongHeading" type="xs:string" sql:field="LongHeading" />
<xs:element name="Date" type="xs:date" sql:field="Date" />
<xs:element name="Contents" type="xs:string" sql:field="Contents" />
<xs:element name="Summary" type="xs:string" sql:field="Summary" />
<xs:element name="Priority" type="xs:string" sql:field="Priority" />
<xs:element name="Picture">
<xs:complexType>
<xs:attribute name ="Orientation" type="xs:string"
sql:field="PicOrientation" />
<xs:attribute name ="PhotoTag" type="xs:string"
sql:field="PicPhotoTag" />
<xs:attribute name ="Ratio" type="xs:float" sql:field="Ratio" />
<xs:attribute name ="PhotoId" type="xs:float" sql:field="PhotoId" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" type="xs:string" sql:field="ArticleID" />
<xs:attribute name="Created" type="xs:string" sql:field="Created" />
</xs:complexType>
</xs:element>
</xs:schema>
xml file...
<?xml version="1.0" ?>
<ROOT>
<Article Created="15:57:28" ID="17845153">
<Heading>645 jobs axed by aerospace firm</Heading>
<LongHeading>Co announces 645 jobs are to go at its Belfast subsidiary,
Shorts</LongHeading>
<Date>24/10/2006</Date>
<Contents><![CDATA[A Canadian aerospace firm has announced it is to cut
645 jobs at its subsidiary]]></Contents>
<Summary><![CDATA[A Canadian aerospace firm has announced it is to cut 645
jobs at its subsidiary]]></Summary>
<Priority>Normal</Priority>
<Picture Orientation="Portrait" PhotoTag="manufactures regional jet
aircraft" Ratio="0.7496251874062968" PhotoID="4002039">
<PhotoTag>manufactures regional jet aircraft</PhotoTag>
<Large Width="225" Height="300">
<URL>http://www.test.co.uk/image1.jpg</URL>
</Large>
<Small Width="75" Height="100">
<URL>http://www.test.co.uk/image2.jpg</URL>
</Small>
</Picture>
</Article>
</ROOT >
Any help greatly appreciated.
Thanks
|
|