SQL/XML Annotation Question - a different take

The annotation section defines the relationship between the tables, that the
Xml Bulk Load component use to load data into the tables. So if it is
missing, XML bulk load does not have a way to identify which table to
populate and which piece data should go to which column.

Do each XML file contain data for a single table? Since you don't have
annotation/relationship information, SSIS might be a better option for you
IMHO.
--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"Travis McGee" <travisGatesMcGee DeleteThis @hotmail.com> wrote in message
news:ezjMi7eAJHA.4748@TK2MSFTNGP06.phx.gbl...
> So..... we have these files from different vendors in XML to be loaded
> into SQL Server.
> Looks like they don't have the annotation/relationship sections at the
> towards the top of the files.
> These are complicated schemas and people who were creating them knew what
> they were doing.
> Is there another way of "creating" the tables out of the xsd's even if the
> schema does not have annotation section and then inserting the xml data.
>
> XML did not arrive to make our lives more difficult since we got along
> well with flat files since the French Revolution, right?
>
>
>
 >> Stay informed about: SQL/XML Annotation Question - a different take 

ok, will look at SSIS.

There aren't any tables. Looked at some of the bigger XML files, they look
very complicated, meaning multiple tables.
That is the basis of my question: two different (non-profit) groups, putting
out Schema's for people like me and we have to sort through huge xsd files
to figure out multiple table relationships...just does not make sense.
My gut feeling says, the xsd has enough schema data in it.

We are not looking for triggers, etc out of the relationship; all I want is
the "raw" data in the tables. There are so many xsd files and corresponding
xml data files. Examining the relationships and modifying the xsd's nearly
not an option - I would think.

It is up to you.....wait until I deal with Integration Services, which did
not want to install itself last week on a Win 2008 with SQL 2008 RTM. Have
another Win2008 machine, will play with that.

Still don't understand, if SQLXML "has to" have the relationship hints, how
SSIS will figure it out without them.

Don't want to ask the data suppliers (European Governments); as getting a
question answered by them takes months.

Thanks a billion for your kind help


"Jacob Sebastian" <jacob.reliancesp RemoveThis @gmail.com> wrote in message
news:1E46AB18-7187-452B-B83E-B382693424FE@microsoft.com...
> The annotation section defines the relationship between the tables, that
> the Xml Bulk Load component use to load data into the tables. So if it is
> missing, XML bulk load does not have a way to identify which table to
> populate and which piece data should go to which column.
>
> Do each XML file contain data for a single table? Since you don't have
> annotation/relationship information, SSIS might be a better option for you
> IMHO.
> --
> Jacob Sebastian
> SQL Server MVP
> http://www.sqlserverandxml.com
>
> "Travis McGee" <travisGatesMcGee RemoveThis @hotmail.com> wrote in message
> news:ezjMi7eAJHA.4748@TK2MSFTNGP06.phx.gbl...
>> So..... we have these files from different vendors in XML to be loaded
>> into SQL Server.
>> Looks like they don't have the annotation/relationship sections at the
>> towards the top of the files.
>> These are complicated schemas and people who were creating them knew what
>> they were doing.
>> Is there another way of "creating" the tables out of the xsd's even if
>> the schema does not have annotation section and then inserting the xml
>> data.
>>
>> XML did not arrive to make our lives more difficult since we got along
>> well with flat files since the French Revolution, right?
>>
>>
>>
 >> Stay informed about: SQL/XML Annotation Question - a different take 

Hi Jacob,
Struggling with SSIS. It does not like the xsd file....but have more
experimentation to do. SSIS itself has gotten to be much more complicated
than the ones I have played with during beta stages of 2008.
But if you care to answer my burning question below...please feel free.
Thanks.....+ will post the result of my efforts probably tomorrow sometime.
Thanks again for all of your help.
Lilya



"Travis McGee" <travisGatesMcGee.DeleteThis@hotmail.com> wrote in message
news:OD%23i6zfAJHA.3728@TK2MSFTNGP03.phx.gbl...
> ok, will look at SSIS.
>
> There aren't any tables. Looked at some of the bigger XML files, they
> look very complicated, meaning multiple tables.
> That is the basis of my question: two different (non-profit) groups,
> putting out Schema's for people like me and we have to sort through huge
> xsd files to figure out multiple table relationships...just does not make
> sense.
> My gut feeling says, the xsd has enough schema data in it.
>
> We are not looking for triggers, etc out of the relationship; all I want
> is the "raw" data in the tables. There are so many xsd files and
> corresponding xml data files. Examining the relationships and modifying
> the xsd's nearly not an option - I would think.
>
> It is up to you.....wait until I deal with Integration Services, which did
> not want to install itself last week on a Win 2008 with SQL 2008 RTM.
> Have another Win2008 machine, will play with that.
>
> Still don't understand, if SQLXML "has to" have the relationship hints,
> how SSIS will figure it out without them.
>
> Don't want to ask the data suppliers (European Governments); as getting a
> question answered by them takes months.
>
> Thanks a billion for your kind help
>
>
> "Jacob Sebastian" <jacob.reliancesp.DeleteThis@gmail.com> wrote in message
> news:1E46AB18-7187-452B-B83E-B382693424FE@microsoft.com...
>> The annotation section defines the relationship between the tables, that
>> the Xml Bulk Load component use to load data into the tables. So if it is
>> missing, XML bulk load does not have a way to identify which table to
>> populate and which piece data should go to which column.
>>
>> Do each XML file contain data for a single table? Since you don't have
>> annotation/relationship information, SSIS might be a better option for
>> you IMHO.
>> --
>> Jacob Sebastian
>> SQL Server MVP
>> http://www.sqlserverandxml.com
>>
>> "Travis McGee" <travisGatesMcGee.DeleteThis@hotmail.com> wrote in message
>> news:ezjMi7eAJHA.4748@TK2MSFTNGP06.phx.gbl...
>>> So..... we have these files from different vendors in XML to be loaded
>>> into SQL Server.
>>> Looks like they don't have the annotation/relationship sections at the
>>> towards the top of the files.
>>> These are complicated schemas and people who were creating them knew
>>> what they were doing.
>>> Is there another way of "creating" the tables out of the xsd's even if
>>> the schema does not have annotation section and then inserting the xml
>>> data.
>>>
>>> XML did not arrive to make our lives more difficult since we got along
>>> well with flat files since the French Revolution, right?
>>>
>>>
>>>
 >> Stay informed about: SQL/XML Annotation Question - a different take 

I guess the question you are referring is the SSIS question "how does ssis
finds the relationship". SSIS does not seem to be able to understand
relationships if your schema does not contain them. It is good for loading
the data to a single table. I have not worked with SSIS much. Probably you
can post this question again in the SSIS forum and get a better answer.

--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"Travis McGee" <travisGatesMcGee.DeleteThis@hotmail.com> wrote in message
news:ObQUFXlAJHA.5176@TK2MSFTNGP04.phx.gbl...
> Hi Jacob,
> Struggling with SSIS. It does not like the xsd file....but have more
> experimentation to do. SSIS itself has gotten to be much more complicated
> than the ones I have played with during beta stages of 2008.
> But if you care to answer my burning question below...please feel free.
> Thanks.....+ will post the result of my efforts probably tomorrow
> sometime.
> Thanks again for all of your help.
> Lilya
>
>
>
> "Travis McGee" <travisGatesMcGee.DeleteThis@hotmail.com> wrote in message
> news:OD%23i6zfAJHA.3728@TK2MSFTNGP03.phx.gbl...
>> ok, will look at SSIS.
>>
>> There aren't any tables. Looked at some of the bigger XML files, they
>> look very complicated, meaning multiple tables.
>> That is the basis of my question: two different (non-profit) groups,
>> putting out Schema's for people like me and we have to sort through huge
>> xsd files to figure out multiple table relationships...just does not make
>> sense.
>> My gut feeling says, the xsd has enough schema data in it.
>>
>> We are not looking for triggers, etc out of the relationship; all I want
>> is the "raw" data in the tables. There are so many xsd files and
>> corresponding xml data files. Examining the relationships and modifying
>> the xsd's nearly not an option - I would think.
>>
>> It is up to you.....wait until I deal with Integration Services, which
>> did not want to install itself last week on a Win 2008 with SQL 2008 RTM.
>> Have another Win2008 machine, will play with that.
>>
>> Still don't understand, if SQLXML "has to" have the relationship hints,
>> how SSIS will figure it out without them.
>>
>> Don't want to ask the data suppliers (European Governments); as getting a
>> question answered by them takes months.
>>
>> Thanks a billion for your kind help
>>
>>
>> "Jacob Sebastian" <jacob.reliancesp.DeleteThis@gmail.com> wrote in message
>> news:1E46AB18-7187-452B-B83E-B382693424FE@microsoft.com...
>>> The annotation section defines the relationship between the tables, that
>>> the Xml Bulk Load component use to load data into the tables. So if it
>>> is missing, XML bulk load does not have a way to identify which table to
>>> populate and which piece data should go to which column.
>>>
>>> Do each XML file contain data for a single table? Since you don't have
>>> annotation/relationship information, SSIS might be a better option for
>>> you IMHO.
>>> --
>>> Jacob Sebastian
>>> SQL Server MVP
>>> http://www.sqlserverandxml.com
>>>
>>> "Travis McGee" <travisGatesMcGee.DeleteThis@hotmail.com> wrote in message
>>> news:ezjMi7eAJHA.4748@TK2MSFTNGP06.phx.gbl...
>>>> So..... we have these files from different vendors in XML to be loaded
>>>> into SQL Server.
>>>> Looks like they don't have the annotation/relationship sections at the
>>>> towards the top of the files.
>>>> These are complicated schemas and people who were creating them knew
>>>> what they were doing.
>>>> Is there another way of "creating" the tables out of the xsd's even if
>>>> the schema does not have annotation section and then inserting the xml
>>>> data.
>>>>
>>>> XML did not arrive to make our lives more difficult since we got along
>>>> well with flat files since the French Revolution, right?
>>>>
>>>>
>>>>
>
>
 >> Stay informed about: SQL/XML Annotation Question - a different take