re: Insert XML document into a table

• Brendon

  Ten Centuries

  Points: 1252

  More actions

  May 4, 2003 at 6:30 pm

  #64465

  Hi,

  I'm having some problems inserting an XML document into an SQL table. I'm using a FORXML statement to read the XML Stream being passed to the stored proc.

  However I also want to pass the table name to load the XML doc into. If I hardcode the table in the query, I have no problems loading it. But if I try to create a query string with my tablename variable in it, I get errors relating to the XML handle @iDoc.

  ie.

  Straight statement works:

  INSERT INTO test_dist_ita_upl (outlet_id, d_input, variant_id, l_status, c_status)

  SELECT * FROM OpenXML @iDoc, '/dist_ita_changes/outl/dist', 0)

  WITH (outl VARCHAR(10) '../@outl',

  dtm varchar(10) '@dtm',

  vari varchar(3) '@vari',

  lst VARCHAR(1) '@lst',

  cst VARCHAR(1) '@cst' )

  This fails:

  SET @sqlquery = 'INSERT INTO test_dist_ita_upl (outlet_id, d_input, variant_id, l_status, c_status)

  SELECT * FROM OpenXML( ''' + convert(char(10), @iDoc) + ''', ''/dist_ita_changes/outl/dist'', 0)

  WITH (outl VARCHAR(10) ''../@outl'',

  dtm varchar(10) ''@dtm'',

  vari varchar(3) ''@vari'',

  lst VARCHAR(1) ''@lst'',

  cst VARCHAR(1) ''@cst'' )'

  EXECUTE sp_executesql @sqlquery

  I'm converting the @iDoc variable to a string in order to concat the query together - does this have an adverse affect on it, or should it be done differently?

  The only way I can see to load the table is to use a cursor, which I really don't want to do.

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  May 4, 2003 at 11:13 pm

  #456114

  A bit of quick and dirty.

  Enhanced from BOL 2000 example

  DECLARE @idoc int,

  @doc varchar(1000),

  @What varchar(1000),

  @tab varchar(80)

  Set @tab='#Tmp'

  Set @What='/ROOT/Customer'

  SET @doc ='

  <ROOT>

  <Customer CustomerID="VINET" ContactName="Paul Henriot">

  <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

  OrderDate="1996-07-04T00:00:00">

  <OrderDetail ProductID="11" Quantity="12"/>

  <OrderDetail ProductID="42" Quantity="10"/>

  </Order>

  </Customer>

  </ROOT>'

  -- Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  -- Execute a SELECT statement using OPENXML rowset provider.

  SELECT * Into #Tmp

  FROM OPENXML (@idoc, @What,1)

  WITH (CustomerID varchar(10),

  ContactName varchar(20))

  EXEC sp_xml_removedocument @idoc

  Select * from #tmp

  Exec ('Select * Into '+'TestTab'+' from #Tmp')

  Select * from TestTab

  Drop Table #tmp

  Drop Table TestTab

  The TestTab reference must become a variable.

• Brendon

  Ten Centuries

  Points: 1252

  More actions

  May 5, 2003 at 1:03 am

  #456115

  Ai, it's a bit dirty but certainly better than using a cursor.

  cheers