XML Inserts

• DanKennedy

  Hall of Fame

  Points: 3736

  More actions

  June 16, 2006 at 1:44 am

  #644169

  nileshsane, I'd recommend using something like SQLXML and bulk loading the files if they are this size.

  ---

  Dan
  www.firstcs.co.uk

• nileshsane

  Default port

  Points: 1465

  More actions

  June 16, 2006 at 2:40 am

  #644180

  @noggin - Yes, that could be an option for bulk inserts. But in our scnario, the inserts would be done on an OLTP database, and the amount of data which is to be inserted in a table would grately vary. Eg, if a order has just 2 items then it could well quality for the 8K limit, but if the number of items is say 20-30 it could exceed the 8K limit.

  Is there a problem in passing the XML directly like this?

  EXEC

  sp_xml_preparedocument @hdoc OUTPUT,'

  <root>

    <Customer>

      <Cust_id>101</Cust_id>

      <Cust_fname>"George"</Cust_fname>

      <Cust_lname>"Spencer"</Cust_lname>

      <Cust_address>"Some Address"</Cust_address>

      <Cust_dob>

         <day>2</day>

         <month>10</month>

         <year>1963</year>

      </Cust_dob>

      <Cust_Spends>

         <cust_currency>"USD"</cust_currency>

         <cust_spendamount>1000</cust_spendamount>

      </Cust_Spends>

    </Customer>

  </root>'

  ---

  What I hear I forget, what I see I remember, what I do I understand

• DanKennedy

  Hall of Fame

  Points: 3736

  More actions

  June 16, 2006 at 2:49 am

  #644182

  Not that I can see. Also, you are right text/ntext datatypes can't be used for local variables but they can be used for stored procedure parameters. You could therefore just create an sp with a parameters for the xml and call that

  ---

  Dan
  www.firstcs.co.uk