Import of XML data.

Dear all,
I have been asked to look into the following.
Data is delivered in XML format.
(Assume wel formed XML and the stylesheet is present).
This data has to be imported in the database.
What are the possibilities in 2005?
What are the possibilities in 2000?
Especially are there enough possibilities to import the XML data 2000. Or do
we need a special workaround voor 2000 ?
My experience with XML is very limited. Once the data is present in the
database I'll will be able to transform the data in such a way that it fits
in the target tables.
Thanks for your time and attention,
Ben Brugman"ben brugman" <ben@.niethier.nl> wrote in message
news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
> Dear all,
> I have been asked to look into the following.
> Data is delivered in XML format.
> (Assume wel formed XML and the stylesheet is present).
> This data has to be imported in the database.
> What are the possibilities in 2005?
> What are the possibilities in 2000?
> Especially are there enough possibilities to import the XML data 2000. Or
> do we need a special workaround voor 2000 ?
> My experience with XML is very limited. Once the data is present in the
> database I'll will be able to transform the data in such a way that it
> fits in the target tables.
> Thanks for your time and attention,
> Ben Brugman
>
2005 has more XML related features, but you can import the data into 2000 as
well.
Take a look at the OPENXML command in the Books Online. You can use those
queries to pull the data from the XML document into whatever format you
wish.
Rick Sawtell|||Thank you, I hadn't thought about this possibility.
Does this say that I can not import directly from XML into 2000?
Because then I do not have to look further into that ally.
Thanks for your time and suggestion,
Ben Brugman
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> schreef in bericht
news:Op%232VF9oIHA.4912@.TK2MSFTNGP03.phx.gbl...
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
>> Dear all,
>> I have been asked to look into the following.
>> Data is delivered in XML format.
>> (Assume wel formed XML and the stylesheet is present).
>> This data has to be imported in the database.
>> What are the possibilities in 2005?
>> What are the possibilities in 2000?
>> Especially are there enough possibilities to import the XML data 2000. Or
>> do we need a special workaround voor 2000 ?
>> My experience with XML is very limited. Once the data is present in the
>> database I'll will be able to transform the data in such a way that it
>> fits in the target tables.
>> Thanks for your time and attention,
>> Ben Brugman
> 2005 has more XML related features, but you can import the data into 2000
> as well.
> Take a look at the OPENXML command in the Books Online. You can use those
> queries to pull the data from the XML document into whatever format you
> wish.
>
> Rick Sawtell
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:7c959$480ce938$53557893$11072@.cache90.multikabel.net...
> Thank you, I hadn't thought about this possibility.
> Does this say that I can not import directly from XML into 2000?
> Because then I do not have to look further into that ally.
> Thanks for your time and suggestion,
> Ben Brugman
>
That depends on your needs. You could put an XML column into a text/ntext
or sufficiently large varchar/nvarchar field in SQL Server 2000. But then
you would be treating the XML as a single column in a table. This can also
be done in 2005, but as an XML data type rather than string datatypes listed
above. 2005 also has other advantages like binding an XSD to the XML data
type.
In order to map the columns in a database table(s) to specific nodes in the
xml object, you would need to use the OPENXML with some XPATH queries.
It is relatively straightforward.
As far as outputting XML, there are several avenues for you to pursue. This
includes the SELECT ... FOR XML scenario as well as some others.
HTH
Rick Sawtell