Re: Inserting into SQL via XML using OLEDB Consumer Templates ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanx Erland.

Will need to do some more reading then :)


Erland Sommarskog wrote: 
Heiko (heiko_dev@xxxxxxxxxx) writes:

Has anyone got a sample or some links on how to insert data into SQL Server via XML ? 


The topic on OPENXML in Books Online should take you a long way.
Else have a look at http://www.sqlxml.org/.


Only saw samples to retrieve XML up to now.

- Ideally I would like to create the XML insert statements in memory,
  not reference an external xml command file.

- If we need to insert between 10 to 10000 rows of data into SQL Server
  table, what options are available to do this from Visual C++ ?
  (Currently we use OLE DB Consumer Templates implementation that calls
  a stored proc for each row insert. )

Is it correct to assume that inserting 3000 rows via XML would be faster (one call) than doing 3000 stored proc calls ? 


Yes, that's a definite winner. We have several places in our app,
where insert data from a grid page or similar. We used to that one
by one, now a lot of that uses OPENXML for improved performance.


.

Relevant Pages

  • Re: XML Buffer, OPENXML Usage Advice
    ... The memory available for OpenXML is 1/8th of available server memory (I ... >attempting to replace some looping processes with batch processes. ... but so far the XML passed into the proc has been less than 2KB ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml seemingly fails to process external entity
    ... > External entities are not supported with OpenXML for a variety of security ... > Best is to resolve them on the client/midtier side before sending the XML to ... >> Here are the beginnings of the lines I see in SQL Query Analyzer: ... >> You can verify that this external entity stuff is done properly by opening ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... Avoid OPENXML over large XML documents. ... Avoid large numbers of concurrent OPENXML statements over XML ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml seemingly fails to process external entity
    ... External entities are not supported with OpenXML for a variety of security ... Best is to resolve them on the client/midtier side before sending the XML to ... here is some SQL that correctly processes some XML that does ...
    (microsoft.public.sqlserver.xml)