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