Showing posts with label bulk. Show all posts
Showing posts with label bulk. Show all posts

Wednesday, March 21, 2012

How come this dont work?

Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
error out for me.
Heres what I have;
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
FIELDTERMINATOR = '~'
)
I am running this thru QA logged in as the Admin.
The file is in the location and the date looks like so, in Notepad.
302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
~ is the field and row terminator.
When I run above cmd I get it telling me it ran successfully.
But theres no data in table and it runs instantly.
Any help would be appreciated.
Thanks
Deasun
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.What is the definition of the table. Also, you didn't specify a row separator. Perhaps the table has
only one columns and you really want ~ as the row terminator?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deasun" <Deasun@.discussions.microsoft.com> wrote in message
news:BCA35F53-B895-4F6A-9435-8AA493BA3338@.microsoft.com...
> Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
> error out for me.
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
> I am running this thru QA logged in as the Admin.
> The file is in the location and the date looks like so, in Notepad.
> 302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
> ~ is the field and row terminator.
> When I run above cmd I get it telling me it ran successfully.
> But theres no data in table and it runs instantly.
> Any help would be appreciated.
> Thanks
> Deasun
> --
> Deasun
> Home Site: www.tirnaog.com
> Check out: The Code Vault in my forums section.|||"Deasun" wrote:
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
That should be:
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
ROWTERMINATOR = '~'
)
ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
get the idea.
Maury|||Thanks to you both for the reply.
I had specified both row and field terminators at one point neither worked.
We now think it has something to do with the SQL server itself.
We have tried the cmd on another server and it worked fine. :)
Thanks again.
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.
"Maury Markowitz" wrote:
> "Deasun" wrote:
> > Heres what I have;
> > BULK INSERT TBL_Master_DoNotCallList_Loader
> > FROM 'E:\DE_210652006.txt'
> > WITH
> > (
> > FIELDTERMINATOR = '~'
> > )
> That should be:
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> ROWTERMINATOR = '~'
> )
> ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
> get the idea.
> Maury
>sql
Posted by clawzxce at 4:51 AM 0 comments
Labels: , , , , , , , , , , ,

Monday, March 19, 2012

How can XML Bulk Load assign automatically a PK to a column?

Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?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"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David GrantThe table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.com...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?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"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant

>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
" PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=Northwind;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant

>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
Posted by clawzxce at 1:04 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

How can XML Bulk Load assign automatically a PK to a column?

Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?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"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David Grant
The table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.c om...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?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"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant
|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant

>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>
|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"PROVIDER=SQLOLEDB;SERVER=(local);DATABASE=Northwi nd;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant

>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
Posted by clawzxce at 1:04 PM 0 comments
Labels: , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)