I can't import XML into SQL with Bulk Load

Hi,
I can′t import a XML file into SQL Server with a XDR mapping schema
specifying relationships.
The Error is:
"Schema: missing mapping foreign-ralation on BoletoConta
Code 80004005
Source: schema mapping "
What is wrong?
***********************************
This is a complete script.
CREATE TABLE Boleto (
IdBoleto int NOT NULL ,
Numero varchar (50) ,
TipoBoletoId varchar (50) ,
) ON PRIMARY
GO
CREATE TABLE dbo.BoletoConta (
ContaId int NULL ,
Referencia varchar (50) ,
Valor varchar (50) ,
)
GO
This is the mapping schema file (XDR).
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Id" content="textonly" />
<ElementType name="Numero" content="textonly" />
<ElementType name="TipoBoletoId" content="textonly" />
<ElementType name="Root" sql:is-constant="1">
<element type="Boleto" />
</ElementType>
<ElementType name="Boleto" sql:relation="Boleto">
<element type="Id" sql:field="IdBoleto" />
<element type="Numero" />
<element type="TipoBoletoId" />
<element type="BoletoConta">
<sql:relationship key-relation="Boleto" key="IdBoleto"
foreing-relation="BoletoConta" foreing-key="IdBoleto" />
</element>
</ElementType>
<ElementType name="BoletoConta" sql:relation="BoletoConta">
<AttributeType name="ContaId" />
<AttributeType name="Referencia" />
<AttributeType name="Valor" />
<attribute type="ContaId" sql:field="ContaId" />
<attribute type="Referencia" sql:field="Referencia" />
<attribute type="Valor" sql:field="Valor" />
</ElementType>
</Schema>
This is a listing of the XML document specified.
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Root>
<Boleto Id="369" Numero="0000000000369" TipoBoletoId="5" >
<BoletoConta ContaId="5" Referencia="01/07/1990" Valor="0.08" />
</Boleto>
<Boleto Id="89627" Numero="0000000089627" TipoBoletoId="5" >
<BoletoConta ContaId="14" Referencia="01/11/2001" Valor="40.55" />
<BoletoConta ContaId="15" Referencia="01/11/2001" Valor="203.10" />
<BoletoConta ContaId="41" Referencia="01/11/2001" Valor="4112.37" />
<BoletoConta ContaId="42" Referencia="01/11/2001" Valor="0.59" />
</Boleto>
<Boleto Id="89628" Numero="0000000089628" TipoBoletoId="5" >
<BoletoConta ContaId="14" Referencia="01/12/2001" Valor="219.56" />
<BoletoConta ContaId="15" Referencia="01/12/2001" Valor="202.80" />
<BoletoConta ContaId="41" Referencia="01/12/2001" Valor="3830.85" />
<BoletoConta ContaId="42" Referencia="01/12/2001" Valor="6.20" />
</Boleto>
</Root>
This is a script to import to SQL-SERVER 2000
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=(local);database=mydb;uid=sa;pwd=
xxx"
objBL.ErrorLogFile = "c:\inetpub\wwwroot\xml\error.log"
objBL.Transaction=true
objBL.Execute "c:\mapa.xml", "c:\dados.xml"
Set objBL = Nothing
Eduardo MaggiI' sorry.
I have found the problem, the sintax "foreing" was wrong the correct is
"foreign".
Don't consider this message.
Eduardo Maggi
"EduardoMaggi" wrote:

> Hi,
> I can′t import a XML file into SQL Server with a XDR mapping schema
> specifying relationships.
> The Error is:
> "Schema: missing mapping foreign-ralation on BoletoConta
> Code 80004005
> Source: schema mapping "
> What is wrong?
> ***********************************
> This is a complete script.
> CREATE TABLE Boleto (
> IdBoleto int NOT NULL ,
> Numero varchar (50) ,
> TipoBoletoId varchar (50) ,
> ) ON PRIMARY
> GO
> CREATE TABLE dbo.BoletoConta (
> ContaId int NULL ,
> Referencia varchar (50) ,
> Valor varchar (50) ,
> )
> GO
> This is the mapping schema file (XDR).
> <?xml version="1.0" ?>
> <Schema xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
> xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <ElementType name="Id" content="textonly" />
> <ElementType name="Numero" content="textonly" />
> <ElementType name="TipoBoletoId" content="textonly" />
> <ElementType name="Root" sql:is-constant="1">
> <element type="Boleto" />
> </ElementType>
> <ElementType name="Boleto" sql:relation="Boleto">
> <element type="Id" sql:field="IdBoleto" />
> <element type="Numero" />
> <element type="TipoBoletoId" />
> <element type="BoletoConta">
> <sql:relationship key-relation="Boleto" key="IdBoleto"
> foreing-relation="BoletoConta" foreing-key="IdBoleto" />
> </element>
> </ElementType>
> <ElementType name="BoletoConta" sql:relation="BoletoConta">
> <AttributeType name="ContaId" />
> <AttributeType name="Referencia" />
> <AttributeType name="Valor" />
> <attribute type="ContaId" sql:field="ContaId" />
> <attribute type="Referencia" sql:field="Referencia" />
> <attribute type="Valor" sql:field="Valor" />
> </ElementType>
> </Schema>
>
> This is a listing of the XML document specified.
>
> <?xml version="1.0" encoding="ISO-8859-1" ?>
> <Root>
> <Boleto Id="369" Numero="0000000000369" TipoBoletoId="5" >
> <BoletoConta ContaId="5" Referencia="01/07/1990" Valor="0.08" />
> </Boleto>
> <Boleto Id="89627" Numero="0000000089627" TipoBoletoId="5" >
> <BoletoConta ContaId="14" Referencia="01/11/2001" Valor="40.55" />
> <BoletoConta ContaId="15" Referencia="01/11/2001" Valor="203.10" />
> <BoletoConta ContaId="41" Referencia="01/11/2001" Valor="4112.37" />
> <BoletoConta ContaId="42" Referencia="01/11/2001" Valor="0.59" />
> </Boleto>
> <Boleto Id="89628" Numero="0000000089628" TipoBoletoId="5" >
> <BoletoConta ContaId="14" Referencia="01/12/2001" Valor="219.56" />
> <BoletoConta ContaId="15" Referencia="01/12/2001" Valor="202.80" />
> <BoletoConta ContaId="41" Referencia="01/12/2001" Valor="3830.85" />
> <BoletoConta ContaId="42" Referencia="01/12/2001" Valor="6.20" />
> </Boleto>
> </Root>
> This is a script to import to SQL-SERVER 2000
> Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=(local);database=mydb;uid=sa;pwd=
xxx"
> objBL.ErrorLogFile = "c:\inetpub\wwwroot\xml\error.log"
> objBL.Transaction=true
> objBL.Execute "c:\mapa.xml", "c:\dados.xml"
> Set objBL = Nothing
>
>
>
> --
> Eduardo Maggi

I can't import XML into SQL with Bulk Load

Hi,
I can′t import a XML file into SQL Server with a XDR mapping schema
specifying relationships.
The Error is:
"Schema: missing mapping foreign-ralation on BoletoConta
Code 80004005
Source: schema mapping "
What is wrong?
***********************************
This is a complete script.
CREATE TABLE Boleto (
IdBoleto int NOT NULL ,
Numero varchar (50) ,
TipoBoletoId varchar (50) ,
) ON PRIMARY
GO
CREATE TABLE dbo.BoletoConta (
ContaId int NULL ,
Referencia varchar (50) ,
Valor varchar (50) ,
)
GO
This is the mapping schema file (XDR).
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Id" content="textonly" />
<ElementType name="Numero" content="textonly" />
<ElementType name="TipoBoletoId" content="textonly" />
<ElementType name="Root" sql:is-constant="1">
<element type="Boleto" />
</ElementType>
<ElementType name="Boleto" sql:relation="Boleto">
<element type="Id" sql:field="IdBoleto" />
<element type="Numero" />
<element type="TipoBoletoId" />
<element type="BoletoConta">
<sql:relationship key-relation="Boleto" key="IdBoleto"
foreing-relation="BoletoConta" foreing-key="IdBoleto" />
</element>
</ElementType>
<ElementType name="BoletoConta" sql:relation="BoletoConta">
<AttributeType name="ContaId" />
<AttributeType name="Referencia" />
<AttributeType name="Valor" />
<attribute type="ContaId" sql:field="ContaId" />
<attribute type="Referencia" sql:field="Referencia" />
<attribute type="Valor" sql:field="Valor" />
</ElementType>
</Schema>
This is a listing of the XML document specified.
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Root>
<Boleto Id="369" Numero="0000000000369" TipoBoletoId="5" >
<BoletoConta ContaId="5" Referencia="01/07/1990" Valor="0.08" />
</Boleto>
<Boleto Id="89627" Numero="0000000089627" TipoBoletoId="5" >
<BoletoConta ContaId="14" Referencia="01/11/2001" Valor="40.55" />
<BoletoConta ContaId="15" Referencia="01/11/2001" Valor="203.10" />
<BoletoConta ContaId="41" Referencia="01/11/2001" Valor="4112.37" />
<BoletoConta ContaId="42" Referencia="01/11/2001" Valor="0.59" />
</Boleto>
<Boleto Id="89628" Numero="0000000089628" TipoBoletoId="5" >
<BoletoConta ContaId="14" Referencia="01/12/2001" Valor="219.56" />
<BoletoConta ContaId="15" Referencia="01/12/2001" Valor="202.80" />
<BoletoConta ContaId="41" Referencia="01/12/2001" Valor="3830.85" />
<BoletoConta ContaId="42" Referencia="01/12/2001" Valor="6.20" />
</Boleto>
</Root>
This is a script to import to SQL-SERVER 2000
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=(local);database=mydb;uid=sa;pwd=xxx"
objBL.ErrorLogFile = "c:\inetpub\wwwroot\xml\error.log"
objBL.Transaction=true
objBL.Execute "c:\mapa.xml", "c:\dados.xml"
Set objBL = Nothing
Eduardo Maggi
I' sorry.
I have found the problem, the sintax "foreing" was wrong the correct is
"foreign".
Don't consider this message.
Eduardo Maggi
"EduardoMaggi" wrote:

> Hi,
> I can′t import a XML file into SQL Server with a XDR mapping schema
> specifying relationships.
> The Error is:
> "Schema: missing mapping foreign-ralation on BoletoConta
> Code 80004005
> Source: schema mapping "
> What is wrong?
> ***********************************
> This is a complete script.
> CREATE TABLE Boleto (
> IdBoleto int NOT NULL ,
> Numero varchar (50) ,
> TipoBoletoId varchar (50) ,
> ) ON PRIMARY
> GO
> CREATE TABLE dbo.BoletoConta (
> ContaId int NULL ,
> Referencia varchar (50) ,
> Valor varchar (50) ,
> )
> GO
> This is the mapping schema file (XDR).
> <?xml version="1.0" ?>
> <Schema xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
> xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <ElementType name="Id" content="textonly" />
> <ElementType name="Numero" content="textonly" />
> <ElementType name="TipoBoletoId" content="textonly" />
> <ElementType name="Root" sql:is-constant="1">
> <element type="Boleto" />
> </ElementType>
> <ElementType name="Boleto" sql:relation="Boleto">
> <element type="Id" sql:field="IdBoleto" />
> <element type="Numero" />
> <element type="TipoBoletoId" />
> <element type="BoletoConta">
> <sql:relationship key-relation="Boleto" key="IdBoleto"
> foreing-relation="BoletoConta" foreing-key="IdBoleto" />
> </element>
> </ElementType>
> <ElementType name="BoletoConta" sql:relation="BoletoConta">
> <AttributeType name="ContaId" />
> <AttributeType name="Referencia" />
> <AttributeType name="Valor" />
> <attribute type="ContaId" sql:field="ContaId" />
> <attribute type="Referencia" sql:field="Referencia" />
> <attribute type="Valor" sql:field="Valor" />
> </ElementType>
> </Schema>
>
> This is a listing of the XML document specified.
>
> <?xml version="1.0" encoding="ISO-8859-1" ?>
> <Root>
> <Boleto Id="369" Numero="0000000000369" TipoBoletoId="5" >
> <BoletoConta ContaId="5" Referencia="01/07/1990" Valor="0.08" />
> </Boleto>
> <Boleto Id="89627" Numero="0000000089627" TipoBoletoId="5" >
> <BoletoConta ContaId="14" Referencia="01/11/2001" Valor="40.55" />
> <BoletoConta ContaId="15" Referencia="01/11/2001" Valor="203.10" />
> <BoletoConta ContaId="41" Referencia="01/11/2001" Valor="4112.37" />
> <BoletoConta ContaId="42" Referencia="01/11/2001" Valor="0.59" />
> </Boleto>
> <Boleto Id="89628" Numero="0000000089628" TipoBoletoId="5" >
> <BoletoConta ContaId="14" Referencia="01/12/2001" Valor="219.56" />
> <BoletoConta ContaId="15" Referencia="01/12/2001" Valor="202.80" />
> <BoletoConta ContaId="41" Referencia="01/12/2001" Valor="3830.85" />
> <BoletoConta ContaId="42" Referencia="01/12/2001" Valor="6.20" />
> </Boleto>
> </Root>
> This is a script to import to SQL-SERVER 2000
> Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=(local);database=mydb;uid=sa;pwd=xxx"
> objBL.ErrorLogFile = "c:\inetpub\wwwroot\xml\error.log"
> objBL.Transaction=true
> objBL.Execute "c:\mapa.xml", "c:\dados.xml"
> Set objBL = Nothing
>
>
>
> --
> Eduardo Maggi

I cant get the xml data type

I have just installed SQL server 2005 - Developer edition and the problem i get is: i dont have xml datatype available to me. I was able to use the xml datatye as long as i was using SQL server Express edition on a different machine. Please note that i have also applies SQL server 2005 SP-1 aswell but found no change in the datatypes available. I need urgent help in this regard.

During the installation i didnot install the reporting services as i did not need them.Has it something to do with it?XML datatype is avaiable for all SQL Server 2005 variation and service packs. Can you be more specific about "i dont have xml datatype available to me" ?|||

Well I meant that when i create a new table through design or query and type xml it generates and error message that xml datatyope is not defined. And here are the configuration of my machine:

OS: Windows XP - SP2

Hardware check: Intel Pentium 3 GHz processor 1 GB DDR RAM

I installed SQL server 2000 first and then SQL server 2005 dev. edition.

|||

Are you sure that you run the script against your SQL Server 2005 instance and not the SQL Server 2000 instance? What do you get if you say

select @.@.version

?

Thanks

Michael

Hyperlink on a report to download a file

I have an XML field (of type XML) in my report's underlying table. I want to
display a hyperlink in my report. When the user clicks on this hyperlink, I
want them to be able to save the xml value as an XML file on disk (i.e.
"Save File As" in MSIE). Has anyone tried this?
Thank you,
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comYou might be able to use javascript to make it open the file in a new
window.|||Any luck on getting this to work? I'm trying to do the same thing but can't
find any way to do it.|||Nope. I even reposted with the subject "XML field content to be downloaded
as a file" in case my first post wasn't clear enough. But no luck.
I'll keep watching and/or post the answer if I ever figure out a way of
doing it.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Elliott" <Elliott@.discussions.microsoft.com> wrote in message
news:A0650A48-7DAB-42AE-A300-8A9DA17E5401@.microsoft.com...
> Any luck on getting this to work? I'm trying to do the same thing but
> can't
> find any way to do it.
>|||This is the closest possible solution I've found so far and it's from a
Microsoft employee:
http://tinyurl.com/2gs6sp
Basically you'd just have to create a separate web page/site that serves up
the file/xml from a hyperlink in the report. Not quite as easy has getting
it right out of the report directly, but it's definitely doable for my
project.
I'd be happy enough if the report would let me just display the xml with an
associated xsl file.|||Displaying the XML content on your report is pretty easy. Just put the
field's value in a gird like you would any other field type (char, int,
float, etc.). It's just that the cell containing the XML gets pretty big.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Elliott" <Elliott@.discussions.microsoft.com> wrote in message
news:3EE4182B-7C2C-4A37-A71F-F007DE1D6A5C@.microsoft.com...
> This is the closest possible solution I've found so far and it's from a
> Microsoft employee:
> http://tinyurl.com/2gs6sp
> Basically you'd just have to create a separate web page/site that serves
> up
> the file/xml from a hyperlink in the report. Not quite as easy has
> getting
> it right out of the report directly, but it's definitely doable for my
> project.
> I'd be happy enough if the report would let me just display the xml with
> an
> associated xsl file.|||Yeah, I realized I could do that. However I need mine formatted for the
users; I didn't want them to see the raw xml. If they could download it
directly from the report and open it in IE (which is default), then they'd
see it formatted like a webpage since the xml references an xsl file. That
would have been my preference.
Anyway, I took Chris Hays' advice and implemented a small ASP.NET site that
serves up the xml file with the xsl. It was pretty painless and took about
an hour.