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="LiveMeetingAttendeesBMO1" />
</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 LiveMeetingAttendeesBMO1.
Heres my code to post to the database:
public void PostToDatabase(ConferenceType confType, MeetingInfo
Meeting)
{
SqlXmlCommand cmd = new SqlXmlCommand(Configuration.ConnectionString);
cmd.RootTag = "ROOT";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.CommandText = "LiveMeetingAttendeesBM01";
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 LiveMeetingAttendeesBMO1 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