transfer Data From Acces: 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>

<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>

<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

Monday, March 19, 2012

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

No comments:

Post a Comment

transfer Data From Acces

Blog Archive

About Me