XML data feed

[billy]

Hello,
I have an XML data feed that I would like to use to create tables in
SQL Server. The xml data feed consists of a large amount of
information that changes on a regualar basis. Is there a way to
automatically create SQL Server tables using the data feed?

Thanks
Billy

[David Portas]

Re: XML data feed

billy wrote:
[color=blue]
> Hello,
> I have an XML data feed that I would like to use to create tables in
> SQL Server. The xml data feed consists of a large amount of
> information that changes on a regualar basis. Is there a way to
> automatically create SQL Server tables using the data feed?
>
> Thanks
> Billy[/color]

In SQL Server 2005 you can use the XML datatype to store XML documents
in a column but I don't think that is what you are asking for.

Deriving a useful relational schema from an arbitrary XML document with
no other information to go on is potentially a very difficult task.
What is the information that changes on a regular basis and how does it
or should it map to tables in your database? Do you know the metadata
in advance or only at runtime? How will users or processes consume the
data if its structure isn't known at design time?

--
David Portas
SQL Server MVP
--

[John Bell]

Re: XML data feed

Hi

You may want to check out

http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_375f.asp

John

"billy" <billy_cormic@h otmail.com> wrote in message
news:1135861730 .505618.132340@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> Hello,
> I have an XML data feed that I would like to use to create tables in
> SQL Server. The xml data feed consists of a large amount of
> information that changes on a regualar basis. Is there a way to
> automatically create SQL Server tables using the data feed?
>
> Thanks
> Billy
>[/color]

[Rob]

Re: XML data feed

David Portas wrote:[color=blue]
> billy wrote:
>
>[color=green]
>>Hello,
>> I have an XML data feed that I would like to use to create tables in
>>SQL Server. The xml data feed consists of a large amount of
>>information that changes on a regualar basis. Is there a way to
>>automatical ly create SQL Server tables using the data feed?
>>
>>Thanks
>>Billy[/color]
>
>
> In SQL Server 2005 you can use the XML datatype to store XML documents
> in a column but I don't think that is what you are asking for.
>
> Deriving a useful relational schema from an arbitrary XML document with
> no other information to go on is potentially a very difficult task.
> What is the information that changes on a regular basis and how does it
> or should it map to tables in your database? Do you know the metadata
> in advance or only at runtime? How will users or processes consume the
> data if its structure isn't known at design time?
>[/color]

I believe using SQL Server Integration Services can solve this IF the
XML document has inline XSD tags or an XSD file is available with each
feed. An XML Source can be added to a data flow task and its output
directed to a table which I also "believe" can be created on the fly
based on the source schema.

Be warned that I have never tried this task. My statement is based on
something I read late at night and hopefully remember clearly. If
someone in the group can confirm or deny my statement, I'll be able to
sleep tonight. :)

In any case, I'm curious to know the application for this type of
functionality as I agree that deriving schema and building tables on the
fly is a challenging approach.

[billy]

Re: XML data feed

I will know that meta data in advance. I think that the tables will
change about once a month or so.

[billy]

Re: XML data feed

Thanks. I think I have checked these out in the past though.. but i am
not sure will take a look.

[billy]

Re: XML data feed

Thanks Rob. I have not yet had a chance to check out SQL Server 2005.
I will most likely do that next week.

[John Bell]

Re: XML data feed

Hi Billy

Unfortunately your question is too vague to give a definite answer. More
information on how you are getting the XML and the contents of the file
would help. Are inserting the data into the same table or a different table?
What is going to change in the XML?

John

"billy" <billy_cormic@h otmail.com> wrote in message
news:1135909518 .039187.185520@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> Thanks. I think I have checked these out in the past though.. but i am
> not sure will take a look.
>[/color]