Hi,

Here's my xml file (pcxppp18.xml) :

<?xml version="1.0" encoding="iso-8859-1"?>
<leadList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<lead>
<user title="M." lastName="Marechal" firstName="Thierry"
address1="81 rue Alfred*Bastian" address4="Cervieres" postcode="05100"
dayPhone="0492204728" emailAddress="thierry.marechal (AT) free (DOT) fr"/>
<contact dealerCode="270501U" type1="true" type2="false"
type3="false" type4="false" comment="Je possède un Peugeot Boxer
entièrement vitré, et je voudrais remplacer certaine vitre par de la
tôle afin d'aménager l'intérieur à ma convenance."
contactMethod="E-mail"/>
</lead>
</leadList>


And here's my xsd file (leads.xsd) :

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name="money">
<xsd:restriction base="xsd:decimal">
<xsd:totalDigits value="20"/>
<xsd:fractionDigits value="2"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="leadList">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="user"
sql:relation="PEUGEOTPAYS_CONTACTS">
<xsd:complexType>
<xsd:attribute name="IdContact" type="xsd:integer"
use="required"/>
<xsd:attribute name="title" type="xsd:string"
use="required" sql:field="Civilite"/>
<xsd:attribute name="lastName" type="xsd:string" use="required"
sql:field="Nom"/>
<xsd:attribute name="firstName" type="xsd:string" use="required"
sql:field="Prenom"/>
<xsd:attribute name="address1" type="xsd:string"
sql:field="Adresse"/>
<xsd:attribute name="address2" type="xsd:string"
sql:field="AdresseComplement"/>
<xsd:attribute name="address3" type="xsd:string"
sql:field="Adresse3"/>
<xsd:attribute name="address4" type="xsd:string" sql:field="Ville"/>
<xsd:attribute name="postcode" type="xsd:string"
sql:field="CodePostal"/>
<xsd:attribute name="country" type="xsd:string" sql:field="Pays"/>
<xsd:attribute name="dayPhone" type="xsd:string"
sql:field="Telephone"/>
<xsd:attribute name="eveningPhone" type="xsd:string"
sql:field="TelephoneSoir"/>
<xsd:attribute name="mobilePhone" type="xsd:string"
sql:field="Portable"/>
<xsd:attribute name="emailAddress" type="xsd:string"
sql:field="Email"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="contact" sql:relation="PEUGEOTPAYS_CONTACTS">
<xsd:complexType>
<xsd:attribute name="dealerCode" type="xsd:string" use="required"
sql:field="CPV"/>
<xsd:attribute name="type1" type="xsd:boolean" use="required"
sql:field="Type1_DI"/>
<xsd:attribute name="type2" type="xsd:boolean" use="required"
sql:field="Type2_PC"/>
<xsd:attribute name="type3" type="xsd:boolean" use="required"
sql:field="Type3_DE"/>
<xsd:attribute name="type4" type="xsd:boolean" use="required"
sql:field="Type4_PA"/>
<xsd:attribute name="comment" type="xsd:string"
sql:field="Commentaires"/>
<xsd:attribute name="dayTime" type="xsd:string"
sql:field="DateContact"/>
<xsd:attribute name="contactMethod" type="xsd:string" use="required"
sql:field="ContactMethode"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


And i use a vbs file to insert the xml file into my tables (sqlserver
2000) which contains lines below :

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.KeepIdentity = False
objBL.Execute "J:\Peugeot Extranet\Site en
Dev\Data\asp\suiviContact\xml\lead\leads.xsd", "J:\Peugeot
Extranet\Site en Dev\Data\asp\suiviContact\xml\lead\pcxppp18.xml"


And when i execute the vbs, i get an error : "a relation is expected
on lead ..."

The "IdContact" attribute is an identuty field of the table
PEUGEOTPAYS_CONTACTS.
My table "PEUGEOTPAYS_CONTACTS" should contain the 2 elements "user"
and "contact" of the element "lead".

Can anyone help me to find what's wrong with my program ?

Thanks in advance

Jeho

The problem is that you're trying to map 2 complex XML elements to the same
table. It looks like you need a User table and a Contact table.

Can you post the tabledefs from your database?

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Jeho" <j.lezy (AT) b2l (DOT) com> wrote

I splitted my table into 2 different ones. Ans i still have a problem
: i cannot make the relationship between the 2.
When i execute the vbs, i get the error "cannot insert null into
column IdUser ..." of table PEUGEOTPAYS_CONTACT

Please have a look.

Tables definition :

CREATE TABLE [dbo].[PEUGEOTPAYS_USER] (
[IdUser] [int] IDENTITY (1, 1) NOT NULL ,
[Civilite] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Nom] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prenom] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adresse] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdresseComplement] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Adresse3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Ville] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CodePostal] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Pays] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[TelephoneSoir] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Portable] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[PEUGEOTPAYS_CONTACT] (
[IdContact] [int] IDENTITY (1, 1) NOT NULL ,
[IdUser] [int] NOT NULL ,
[CPV] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type1_DI] [bit] NULL ,
[Type2_PC] [bit] NULL ,
[Type3_DE] [bit] NULL ,
[Type4_PA] [bit] NULL ,
[Commentaires] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateContact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactMethode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[PEUGEOTPAYS_CONTACT] WITH NOCHECK ADD
CONSTRAINT [PK_PEUGEOTPAYS_CONTACT] PRIMARY KEY CLUSTERED
(
[IdContact]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PEUGEOTPAYS_USER] WITH NOCHECK ADD
CONSTRAINT [PK_PEUGEOTPAYS_USER] PRIMARY KEY CLUSTERED
(
[IdUser]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PEUGEOTPAYS_CONTACT] ADD
CONSTRAINT [FK_PEUGEOTPAYS_CONTACT_PEUGEOTPAYS_USER] FOREIGN KEY
(
[IdUser]
) REFERENCES [dbo].[PEUGEOTPAYS_USER] (
[IdUser]
)
GO


XML File :

<?xml version="1.0" encoding="iso-8859-1"?>

<leadList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<lead>
<user title="M." lastName="Marechal" firstName="Thierry"
address1="81 rue Alfred*Bastian" address4="Cervieres" postcode="05100"
dayPhone="0492204728" emailAddress="thierry.marechal (AT) free (DOT) fr"/>
<contact dealerCode="270501U" type1="true" type2="false"
type3="false" type4="false" comment="Je possède un Peugeot Boxer
entièrement vitré, et je voudrais remplacer certaine vitre par de la
tôle afin d'aménager l'intérieur à ma convenance.
J'aimerais donc savoir si vous commercialiser un système (tôle
remplaçant la fenêtre ...) me permettant de réaliser ce système?

En vous remerciant.

Thierry Maréchal" contactMethod="E-mail"/>
</lead>
</leadList>



XSL File :

<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="UserContact"
parent="PEUGEOTPAYS_USER"
parent-key="IdUser"
child="PEUGEOTPAYS_CONTACT"
child-key="IdUser" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="lead" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="user" sql:relation="PEUGEOTPAYS_USER" >
<xsd:complexType>
<xsd:attribute name="IdUser" type="xsd:integer" use="required"/>
<xsd:attribute name="title" type="xsd:string" use="required"
sql:field="Civilite"/>
<xsd:attribute name="lastName" type="xsd:string" use="required"
sql:field="Nom"/>
<xsd:attribute name="firstName" type="xsd:string" use="required"
sql:field="Prenom"/>
<xsd:attribute name="address1" type="xsd:string"
sql:field="Adresse"/>
<xsd:attribute name="address2" type="xsd:string"
sql:field="AdresseComplement"/>
<xsd:attribute name="address3" type="xsd:string"
sql:field="Adresse3"/>
<xsd:attribute name="address4" type="xsd:string"
sql:field="Ville"/>
<xsd:attribute name="postcode" type="xsd:string"
sql:field="CodePostal"/>
<xsd:attribute name="country" type="xsd:string"
sql:field="Pays"/>
<xsd:attribute name="dayPhone" type="xsd:string"
sql:field="Telephone"/>
<xsd:attribute name="eveningPhone" type="xsd:string"
sql:field="TelephoneSoir"/>
<xsd:attribute name="mobilePhone" type="xsd:string"
sql:field="Portable"/>
<xsd:attribute name="emailAddress" type="xsd:string"
sql:field="Email"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="contact" sql:relation="PEUGEOTPAYS_CONTACT"
sql:relationship="UserContact" >
<xsd:complexType>
<xsd:attribute name="IdContact" type="xsd:integer"
use="required"/>
<xsd:attribute name="IdUser" type="xsd:integer" use="required"/>
<xsd:attribute name="dealerCode" type="xsd:string" use="required"
sql:field="CPV"/>
<xsd:attribute name="type1" type="xsd:boolean" use="required"
sql:field="Type1_DI"/>
<xsd:attribute name="type2" type="xsd:boolean" use="required"
sql:field="Type2_PC"/>
<xsd:attribute name="type3" type="xsd:boolean" use="required"
sql:field="Type3_DE"/>
<xsd:attribute name="type4" type="xsd:boolean" use="required"
sql:field="Type4_PA"/>
<xsd:attribute name="comment" type="xsd:string"
sql:field="Commentaires"/>
<xsd:attribute name="dayTime" type="xsd:string"
sql:field="DateContact"/>
<xsd:attribute name="contactMethod" type="xsd:string"
use="required" sql:field="ContactMethode"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>



As you can notice, i have created a relationship between
PEUGEOTPAYS_USER and PEUGEOTPAYS_CONTACT, in order to fill the
"IdUser" field of PEUGEOTPAYS_CONTACT table with the "IdUser" of
PEUGEOTPAYS_USER.

Of course, the 2 elements "user" and "contact" are linked since they
belong to the same element "lead".

How can i solve this problem, please ?

Thanks for helping

Jeho

"Graeme Malcolm \(Content Master Ltd.\)" <graemem_cm (AT) hotmail (DOT) com> wrote

You need to nest the child table under the parent in the XML. Change the
schema to this:
<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="UserContact" parent="PEUGEOTPAYS_USER"
parent-key="IdUser" child="PEUGEOTPAYS_CONTACT"
child-key="IdUser" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="lead" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="user" sql:relation="PEUGEOTPAYS_USER">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="contact" sql:relation="PEUGEOTPAYS_CONTACT"
sql:relationship="UserContact">
<xsd:complexType>
<xsd:attribute name="IdContact" type="xsd:integer"
use="required" />
<xsd:attribute name="IdUser" type="xsd:integer"
use="required" />
<xsd:attribute name="dealerCode" type="xsd:string"
use="required" sql:field="CPV" />
<xsd:attribute name="type1" type="xsd:boolean"
use="required" sql:field="Type1_DI" />
<xsd:attribute name="type2" type="xsd:boolean"
use="required" sql:field="Type2_PC" />
<xsd:attribute name="type3" type="xsd:boolean"
use="required" sql:field="Type3_DE" />
<xsd:attribute name="type4" type="xsd:boolean"
use="required" sql:field="Type4_PA" />
<xsd:attribute name="comment" type="xsd:string"
sql:field="Commentaires" />
<xsd:attribute name="dayTime" type="xsd:string"
sql:field="DateContact" />
<xsd:attribute name="contactMethod" type="xsd:string"
use="required" sql:field="ContactMethode" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="IdUser" type="xsd:integer" use="required"
/>
<xsd:attribute name="title" type="xsd:string" use="required"
sql:field="Civilite" />
<xsd:attribute name="lastName" type="xsd:string" use="required"
sql:field="Nom" />
<xsd:attribute name="firstName" type="xsd:string" use="required"
sql:field="Prenom" />
<xsd:attribute name="address1" type="xsd:string"
sql:field="Adresse" />
<xsd:attribute name="address2" type="xsd:string"
sql:field="AdresseComplement" />
<xsd:attribute name="address3" type="xsd:string"
sql:field="Adresse3" />
<xsd:attribute name="address4" type="xsd:string"
sql:field="Ville" />
<xsd:attribute name="postcode" type="xsd:string"
sql:field="CodePostal" />
<xsd:attribute name="country" type="xsd:string" sql:field="Pays"
/>
<xsd:attribute name="dayPhone" type="xsd:string"
sql:field="Telephone" />
<xsd:attribute name="eveningPhone" type="xsd:string"
sql:field="TelephoneSoir" />
<xsd:attribute name="mobilePhone" type="xsd:string"
sql:field="Portable" />
<xsd:attribute name="emailAddress" type="xsd:string"
sql:field="Email" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

and change the XML to this:
<?xml version="1.0" encoding="iso-8859-1" ?>
<leadList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<lead>
<user title="M." lastName="Marechal" firstName="Thierry" address1="81
rue Alfred Bastian"
address4="Cervieres" postcode="05100" dayPhone="0492204728"
emailAddress="thierry.marechal (AT) free (DOT) fr">
<contact dealerCode="270501U" type1="true" type2="false" type3="false"
type4="false" comment="Je possède un Peugeot Boxer
entièrement vitré, et je voudrais remplacer certaine vitre par de la
tôle afin d'aménager l'intérieur à ma convenance.
J'aimerais donc savoir si vous commercialiser un système (tôle
remplaçant la fenêtre ...) me permettant de réaliser ce système?

En vous remerciant.

Thierry Maréchal" contactMethod="E-mail" />
</user>
</lead>
</leadList>

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Jeho" <j.lezy (AT) b2l (DOT) com> wrote

Thanks for your answer, Graeme.

But i cannot modify the xml file; i get it from a client and i have to
insert it into sqlserver 2000.
If there's no other solution, i would have to parse the xml before
inserting it.

There's no real relationship between PEUGEOTPAYS_USER and
PEUGEOTPAYS_CONTACT. They only refer to the same element LEAD. That's
why i created a single table at the beginning.

Is there any other alternative to solve it ?

Thanks a lot

Jeho

"Graeme Malcolm \(Content Master Ltd.\)" <graemem_cm (AT) hotmail (DOT) com> wrote

You could apply an XSL stylesheet to transform the data before bulk loading.

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Jeho" <j.lezy (AT) b2l (DOT) com> wrote

Have you got any idea of how i can do this ?
I have found examples of xsl sheets used with xml files to display html pages.
I don't want to display my files, but just want to insert them into sqlserver.
I have no idea for the moment of how i can do it.
Can you please give me some hints ?

Thanks a lot

Jeho


"Graeme Malcolm \(Content Master Ltd.\)" <graemem_cm (AT) hotmail (DOT) com> wrote

Bryant Likes has an article on this on his FAQ at
http://sqlxml.org/faqs.aspx?faq=49.

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Jeho" <j.lezy (AT) b2l (DOT) com> wrote

ok, i have created an xsl file to transform my xml and i use the
SQLXMLBulkLoad object to insert it into sqlserver.
This works finely with a test file which size is 4 KB. But, when i try
it with a file which size is 322KB, it stucks; no error at all, but
the job is not done, even the new xml file is not created.

Is there any size restriction using MICROSOFT.XMLDOM or SQLXMLBulkLoad
objects ?

Have a look to my vbs program :

ficXml = "pcxppp18.xml"
ficXsl = "pcxppp18.xsl"

Set objXmlDoc = CreateObject("MICROSOFT.XMLDOM")
Set objXslDoc = CreateObject("MICROSOFT.XMLDOM")
objXmlDoc.async = false
objXslDoc.async = false

If objXmlDoc.load(ficXml) = true then
objXmlDoc.load(ficXml)
objXslDoc.load(ficXsl)

strNewXml = objXmlDoc.transformNode(objXslDoc)
Set FSO = CreateObject("Scripting.FileSystemObject")
Set NewFicXml = FSO.CreateTextFile("pcxppp18_new.xml", True)
NewFicXml.WriteLine("<?xml version='1.0' encoding='iso-8859-1'?>")
NewFicXml.WriteLine(strNewXml)
NewFicXml.close

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=SERV-IIS5;database=PEUGEOT_EXTRANET;uid=sa;pwd="
objBL.ErrorLogFile = "error.log"
objBL.KeepIdentity = False

objBL.Execute "leads.xsd", "pcxppp18_new.xml"
Set objBL = Nothing
msgbox "Fin"
Else
Set objXmlDoc = nothing
Set objXslDoc = nothing
End If

Set objXmlDoc = nothing
Set objXslDoc = nothing



Thanks in advance

Jeho

Can you post an example of the XML, the XSL, and the table defs?

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Jeho" <j.lezy (AT) b2l (DOT) com> wrote