Alternative to OpenXML

Hi,
I want to enter data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
Shilpa
hi Shilpa,
Shilpa wrote:
> Hi,
> I want to enter data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
> Regards,
> Shilpa
OpenXML is a buuilt-in function to return an XML Document as a rowset, thus
returning it in a relational way, and the ourput will be rows and columns...
depending on your schema, you do not need it to insert XML documents into
your database if you store an entire XML document in an (n)varchar or
(n)text column...
but if you have a relational schema modelled "the old way", where 1st normal
form excluded hyerarchical data as: "a relvar in in 1st if and only if, in
every legal value of that relvar, every tuple contains exactly one value for
each attribute" (C.J. Date, "An introduction to Database Systems", Addison
Wesley, p. 358), then this is a way to feed an INSERT INTO statement with an
XML document to shred into relational model like
INSERT INTO some_object (<col_list>)
SELECT <col_list>
FROM OPENXML(@.xmlDoc, 'XPath query', n_flags)
WITH (<mappings>);
the XML parser is then loaded and executes the XPath query, returnig the
required results that will be "translated" by SQL Server engine into
relational data to feed the INSERT INTO statement...
if you can/want not use this feature, you can always resort on client
processing, thus traversing as desired the XML Document in your client
application(s) (that can even be hosted and executed on the same server as
SQL Server) via your preferred DOM or SAX parser, extracting the node's
values (and eventually pre-processing them) in order to feed a stored
procedure to insert new data (or dynamic client SQL insert statements, but
storde procedure use should be preferred)...
another way is the use of XML Bulk Load component, part of the Web Release 1
of SQL Server 2000,
http://msdn.microsoft.com/library/de...kload_7pv0.asp
SQL Server 2005 based engines, and thus SQLExpress, include a new data type
explicitely dedicated for XML data, the new XML datatype,
http://msdn2.microsoft.com/en-us/library/ms189887.aspx, where everything
should be easely managed..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
ShilpaWhat product are you using? XML is natively supported in SQL 2005. What
exactly are you trying to achieve?
ML
http://milambda.blogspot.com/

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
ShilpaHello Shilpa,

> I want to entere data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
A lot depends on what you already have (is it a single file, many files,
is it coming in a stream from pipe, etc) and what you want to do with once
you have in SQL Server (e.g., you going to try to parse the XML into relatio
nal
tables or do you just want to store the data as is in one table?)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Another question is whether you are using SQL Server 2000 or 2005.
I would recommend that you look at the SQLXML components SQLXML Bulkload
functionality...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7419f078c817ae36f52190@.news.microsoft.com...
> Hello Shilpa,
>
> A lot depends on what you already have (is it a single file, many files,
> is it coming in a stream from pipe, etc) and what you want to do with once
> you have in SQL Server (e.g., you going to try to parse the XML into
> relational tables or do you just want to store the data as is in one
> table?)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Alternative to OpenXML

Hi,
I want to entere data in xml into tables in the database.
Howevere, I do not want to use OpenXML
Please let me know if there is an alternative.
Regards,
Shilpa
Hello Shilpa,

> I want to entere data in xml into tables in the database.
> Howevere, I do not want to use OpenXML
> Please let me know if there is an alternative.
A lot depends on what you already have (is it a single file, many files,
is it coming in a stream from pipe, etc) and what you want to do with once
you have in SQL Server (e.g., you going to try to parse the XML into relational
tables or do you just want to store the data as is in one table?)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Another question is whether you are using SQL Server 2000 or 2005.
I would recommend that you look at the SQLXML components SQLXML Bulkload
functionality...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7419f078c817ae36f52190@.news.microsoft.co m...
> Hello Shilpa,
>
> A lot depends on what you already have (is it a single file, many files,
> is it coming in a stream from pipe, etc) and what you want to do with once
> you have in SQL Server (e.g., you going to try to parse the XML into
> relational tables or do you just want to store the data as is in one
> table?)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>