transfer Data From Acces: assign

Showing posts with label assign. Show all posts

Friday, March 30, 2012

How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-KimTo add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim
>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>> I have existing data in a table and would like to
>> assign auto number to a column.
>> How can I do?
>> Thank you for your help in advance!
>> -Kim
>
>.
>|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
--
David G.|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim
>--Original Message--
>Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>|||Kim wrote:
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
>> --Original Message--
>> Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>>
>> If you know the number of possible col3 values beforehand, you can
>> write a T-SQL script to move through the table, grab each row, one
>> at a time, check the col3 value, increment the corresponding counter
>> value in the script, and update the row using the counter value.
>> Pseudo-Code Here:
>> Start all counters at 0
>> Loop through a cursor on the table (do this off-hours)
>> Get col3 value
>> If col3 = 'A' then
>> CounterA = CounterA + 1
>> NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
>> If col3 = 'B' Then
>> CounterB = CounterB + 1
>> etc.
>> Update Table
>> Set Col1 = NewCol1
>> Where PKVal = WhateverThePKValueIs
>> --
>> David G.
>> .
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.

How do I assign nos for column

how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

Friday, March 23, 2012

How could I assign rank.

I have the below query

select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]
order by sum (FIWORKING."GROSPFT") DESC

Which returns below

name1 65784
name2 32586
name3 37892

based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
for NAME1, NAME2, etc

I would like to store this in table GROSPFTRANK

That Table looks like

name1 1
name2 2

I haven't been able to figure out the SQL to do this.

Thanks for an help

ChrisYou can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.|||There are several ways to skin this cat.

One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

Insert into GROSPFTRANK ([FI NAME], [RANK])
select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
from
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGOUTER
inner join
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGSUB
on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

blindman

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>

<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

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>

<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);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

Friday, March 30, 2012

How do I assign nos for column

How do I assign nos for column

How do I assign nos for column

how do I assign a string to a parameter Im passing to a select statement?

Friday, March 23, 2012

How could I assign rank.

Monday, March 19, 2012

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

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

transfer Data From Acces

Blog Archive

About Me