MS SQL XML - SqlXml Bulk Upload Lookups

Author

SqlXml Bulk Upload Lookups

Steven Livingstone

2005-12-23, 11:24 am

Folks - been a while since i have used SqlXml 3.0 Bulk Upload and i
have an issue. I don't think it's possible to do using SqlXml 3.0, but
want to confirm.

I have an Xml structure something like:

<data>
<itemA />
<itemB />
<LookupItems>
<LKItemData />
<LKItemData />
<LookupItems>
</data>

My annotated schema does everything correctly, inserting itemA and
itemB into table A up until a point.

I have a column in table A (which is not the identity column) and it
references the identit(ies) of table B (a look up table). So, for every
LKItemData, a duplicated row will be created, with the identity that is
returned from the insert of each the LKItemData value (in table B).

The issue is that although these lookup items are children, they
actually happen first and somehow have to pass their identity back into
the context of the main table A insert (i guess like a sql:inverse
which bulk upload doesn't seem to support).

I don't think this can be done in SqlXml directly and so intend to
write my own (which won't be too much trouble).

I just want to confirm that this kind of ordering is a limitation of
SqlXml and not of my currently holidized brain.

steven :: http://stevenR2.com

Tom Moreau

2005-12-23, 11:24 am

Check out:

http://msdn2.microsoft.com/en-us/library/ms171806.aspx

Example D....

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@cips.ca
www.pinpub.com

"Steven Livingstone" <connect AT stevenR2 DOT com> wrote in message
news:uqxrTT9BGHA.1180@TK2MSFTNGP09.phx.gbl...
Folks - been a while since i have used SqlXml 3.0 Bulk Upload and i
have an issue. I don't think it's possible to do using SqlXml 3.0, but
want to confirm.

I have an Xml structure something like:

<data>
<itemA />
<itemB />
<LookupItems>
<LKItemData />
<LKItemData />
<LookupItems>
</data>

My annotated schema does everything correctly, inserting itemA and
itemB into table A up until a point.

I have a column in table A (which is not the identity column) and it
references the identit(ies) of table B (a look up table). So, for every
LKItemData, a duplicated row will be created, with the identity that is
returned from the insert of each the LKItemData value (in table B).

The issue is that although these lookup items are children, they
actually happen first and somehow have to pass their identity back into
the context of the main table A insert (i guess like a sql:inverse
which bulk upload doesn't seem to support).

I don't think this can be done in SqlXml directly and so intend to
write my own (which won't be too much trouble).

I just want to confirm that this kind of ordering is a limitation of
SqlXml and not of my currently holidized brain.

steven :: http://stevenR2.com

Steven Livingstone

2005-12-27, 7:23 am

Thanks - must have been the pre-Christmas festivities!

Have the "inverse" attribute in the wrong place... modified and works great.

Thanks.
steven

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ekk8Va9BGHA.1544@TK2MSFTNGP10.phx.gbl...
> Check out:
>
> http://msdn2.microsoft.com/en-us/library/ms171806.aspx
>
> Example D....
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@cips.ca
> www.pinpub.com
>
> "Steven Livingstone" <connect AT stevenR2 DOT com> wrote in message
> news:uqxrTT9BGHA.1180@TK2MSFTNGP09.phx.gbl...
> Folks - been a while since i have used SqlXml 3.0 Bulk Upload and i
> have an issue. I don't think it's possible to do using SqlXml 3.0, but
> want to confirm.
>
> I have an Xml structure something like:
>
> <data>
> <itemA />
> <itemB />
> <LookupItems>
> <LKItemData />
> <LKItemData />
> <LookupItems>
> </data>
>
> My annotated schema does everything correctly, inserting itemA and
> itemB into table A up until a point.
>
> I have a column in table A (which is not the identity column) and it
> references the identit(ies) of table B (a look up table). So, for every
> LKItemData, a duplicated row will be created, with the identity that is
> returned from the insert of each the LKItemData value (in table B).
>
> The issue is that although these lookup items are children, they
> actually happen first and somehow have to pass their identity back into
> the context of the main table A insert (i guess like a sql:inverse
> which bulk upload doesn't seem to support).
>
> I don't think this can be done in SqlXml directly and so intend to
> write my own (which won't be too much trouble).
>
> I just want to confirm that this kind of ordering is a limitation of
> SqlXml and not of my currently holidized brain.
>
> steven :: http://stevenR2.com
>
>

Steven Livingstone

2005-12-27, 7:23 am

Tom, a slight irritation, which i suspected may happen is that items with
the same name are input multiple times. This is kinda what i was getting at
before.

Is there any easy way to ensure it uses an existing key if the record being
inserted already exists?
So if "UK" exists, then i get its ID back rather than two inserts. In other
words, is there a way to define record uniqueness (e.g. values matching on
the "Country" column should reuse the ID) rather than simply replying on
identity columns (which seem to duplicate data).

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ekk8Va9BGHA.1544@TK2MSFTNGP10.phx.gbl...
> Check out:
>
> http://msdn2.microsoft.com/en-us/library/ms171806.aspx
>
> Example D....
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@cips.ca
> www.pinpub.com
>
> "Steven Livingstone" <connect AT stevenR2 DOT com> wrote in message
> news:uqxrTT9BGHA.1180@TK2MSFTNGP09.phx.gbl...
> Folks - been a while since i have used SqlXml 3.0 Bulk Upload and i
> have an issue. I don't think it's possible to do using SqlXml 3.0, but
> want to confirm.
>
> I have an Xml structure something like:
>
> <data>
> <itemA />
> <itemB />
> <LookupItems>
> <LKItemData />
> <LKItemData />
> <LookupItems>
> </data>
>
> My annotated schema does everything correctly, inserting itemA and
> itemB into table A up until a point.
>
> I have a column in table A (which is not the identity column) and it
> references the identit(ies) of table B (a look up table). So, for every
> LKItemData, a duplicated row will be created, with the identity that is
> returned from the insert of each the LKItemData value (in table B).
>
> The issue is that although these lookup items are children, they
> actually happen first and somehow have to pass their identity back into
> the context of the main table A insert (i guess like a sql:inverse
> which bulk upload doesn't seem to support).
>
> I don't think this can be done in SqlXml directly and so intend to
> write my own (which won't be too much trouble).
>
> I just want to confirm that this kind of ordering is a limitation of
> SqlXml and not of my currently holidized brain.
>
> steven :: http://stevenR2.com
>
>

Tom Moreau

2005-12-27, 9:23 am

Hmm. You may have to break things up - or do the bulk load into staging
tables and then do INSERT/UPDATE via SQL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@cips.ca
www.pinpub.com

"Steven Livingstone" <connect@NOSPAM.stevenR2.com> wrote in message
news:uiOK%23ktCGHA.208@tk2msftngp13.phx.gbl...
Tom, a slight irritation, which i suspected may happen is that items with
the same name are input multiple times. This is kinda what i was getting at
before.

Is there any easy way to ensure it uses an existing key if the record being
inserted already exists?
So if "UK" exists, then i get its ID back rather than two inserts. In other
words, is there a way to define record uniqueness (e.g. values matching on
the "Country" column should reuse the ID) rather than simply replying on
identity columns (which seem to duplicate data).

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ekk8Va9BGHA.1544@TK2MSFTNGP10.phx.gbl...
> Check out:
>
> http://msdn2.microsoft.com/en-us/library/ms171806.aspx
>
> Example D....
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@cips.ca
> www.pinpub.com
>
> "Steven Livingstone" <connect AT stevenR2 DOT com> wrote in message
> news:uqxrTT9BGHA.1180@TK2MSFTNGP09.phx.gbl...
> Folks - been a while since i have used SqlXml 3.0 Bulk Upload and i
> have an issue. I don't think it's possible to do using SqlXml 3.0, but
> want to confirm.
>
> I have an Xml structure something like:
>
> <data>
> <itemA />
> <itemB />
> <LookupItems>
> <LKItemData />
> <LKItemData />
> <LookupItems>
> </data>
>
> My annotated schema does everything correctly, inserting itemA and
> itemB into table A up until a point.
>
> I have a column in table A (which is not the identity column) and it
> references the identit(ies) of table B (a look up table). So, for every
> LKItemData, a duplicated row will be created, with the identity that is
> returned from the insert of each the LKItemData value (in table B).
>
> The issue is that although these lookup items are children, they
> actually happen first and somehow have to pass their identity back into
> the context of the main table A insert (i guess like a sql:inverse
> which bulk upload doesn't seem to support).
>
> I don't think this can be done in SqlXml directly and so intend to
> write my own (which won't be too much trouble).
>
> I just want to confirm that this kind of ordering is a limitation of
> SqlXml and not of my currently holidized brain.
>
> steven :: http://stevenR2.com
>
>

Steven Livingstone

2005-12-29, 7:23 am

I suspected as much. I may just run a simple normalization routine to
re-map repeated identity fields.

thanks for your advice.

steven :: http://stevenR2.com

Tom Moreau wrote:

> Hmm. You may have to break things up - or do the bulk load into
> staging tables and then do INSERT/UPDATE via SQL.

--

Sponsored Links