MS SQL XML - SqlXmlException - Schema: relationship expected on <element name>. Help!

Author

SqlXmlException - Schema: relationship expected on <element name>. Help!

Json

2005-09-21, 1:26 pm

Ok, brand new to SQLXML 3.0 and its various issues. Heres what I'm
trying to do:

1. I am trying to load xml data into an empty SQL table from my .NET
console application.
2. This isn't a huge amount of data so I'm not interested in
BulkXMLLoad (or whatever its called).
3. I have the xml and xml schema ready to go, with (I hope) the proper
sql annotations.

Heres my xml schema (built via xsd tool with added sql annotations):

<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-micrsooft-com:mapping-schema">
<xs:element name="Visitors" nillable="true" type="VisitorList"
sql:is-constant="1" />
<xs:complexType name="VisitorList">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Visitor"
type="VisitorInfo" sql:relation=" LiveMeetingAttendees
BMO1" />
</xs:sequence>
<xs:attribute name="Count" type="xs:int" use="required"
sql:mapped="false" />
</xs:complexType>
<xs:complexType name="VisitorInfo">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="ID"
type="xs:string" sql:mapped="false" />
<xs:element minOccurs="0" maxOccurs="1" name="userName"
type="xs:string" sql:field="Name" />
<xs:element minOccurs="0" maxOccurs="1" name="Role"
type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="email"
type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="company"
type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="ipAddress"
type="xs:string" sql:field="[IP Address]" />
<xs:element minOccurs="0" maxOccurs="1" name="browser"
type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="meetingName"
type="xs:string" sql:field="[Meeting ID]" />
<xs:element minOccurs="1" maxOccurs="1" name="duration"
type="xs:int" />
<xs:element minOccurs="1" maxOccurs="1" name="meetingTime"
type="xs:dateTime" sql:field="[Activity Day and Time]" />
<xs:element minOccurs="1" maxOccurs="1" name="startTime"
type="xs:dateTime" sql:field="Arrived" />
</xs:sequence>
</xs:complexType>
</xs:schema>

As you can see, one Visitor record should apply to one record in the
backend SQL table, named LiveMeetingAttendees
BMO1.

Heres my code to post to the database:

public void PostToDatabase(Confe
renceType confType, MeetingInfo
Meeting)
{
SqlXmlCommand cmd = new SqlXmlCommand(Config
uration.ConnectionString);
cmd.RootTag = "ROOT";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.CommandText = " LiveMeetingAttendees
BM01";
cmd.SchemaPath = "../../XML/VisitorList_Events.xsd";

DataSet dataset = new DataSet();
SqlXmlAdapter adapter = new SqlXmlAdapter(cmd);
adapter.Fill(dataset); //-- ERROR HITS HERE

//this serializes class into XML according to schema above.
XmlTextReader reader = ConvertToXML();

//now load dataset with new records and update database!
dataset.ReadXml(reader, XmlReadMode.Auto);
adapter.Update(dataset);
}

The "adapter.Fill(dataset)" line causes the error - SqlXmlException:
Schema: relationship expected on 'Visitor'.

I'm not sure why this is appearing... there is no join here, so I don't
need a relationship right? I also map my Visitor elements to records in
the LiveMeetingAttendees
BMO1 table via the "sql:relation" attribute.

Has anyone seen anything like this?

NOTE: The sql:is-constant='1' attribute in the schema - the results are
the same with or without it.

Thanks for the help!! :)

J'son

Json

2005-09-21, 8:24 pm

Some clarity after banging my head on my desk:

I think that since my initial table in the SQL database is empty, the
fill code is unnecessary. Also, in reading some more I figured out that
the CommandText refers to the XSD file of the SqlXmlCommand when using
CommandType = XPath (duh!). So here is the updated PostToDatabase()
function:

public void PostToDatabase(Confe
renceType confType, MeetingInfo
Meeting)
{
SqlXmlCommand cmd = new
SqlXmlCommand(Config
uration.ConnectionString);
cmd.CommandText = "Visitor";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "../../XML/VisitorList_Events.xsd";

DataSet dataset = new DataSet();
SqlXmlAdapter adapter = new SqlXmlAdapter(cmd);

//this serializes class into XML according to schema above.
XmlTextReader reader = ConvertToXML();

dataset.ReadXmlSchema(cmd.SchemaPath);
dataset.ReadXml(reader, XmlReadMode.ReadSchema);
dataset = dataset. GetChanges(DataRowSt
ate.Added);

adapter.Update(dataset); //-- ERROR NOW HAPPENS
HERE (SAME ERROR)
}

Same error occurs, just now on the adapter.Update method so its still
an issue with my schema.

Thanx!

Sincerely,

J'son

Json

2005-09-21, 8:24 pm

Figured it out... check out my urn for the xmlns:sql namespace in the
schema - micrsooft! Arrrggh... :P

J'son

Sponsored Links