New to xml... Is sp_xml_preparedocument required?

• peterzeke

  SSCrazy Eights

  Points: 9003

  More actions

  October 29, 2009 at 6:37 am

  #134953

  I'm finally experimenting with xml, specifically with passing a dataset/temp table to a stored procedure using xml.

  I've read examples that show the use of sp_xml_preparedocument as if it is a required procedure for creating an xml document EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc , and then the subsequent use of sp_xml_removedocument to clear the xml document from sql server's memory EXEC sp_xml_removedocument @handle

  I've discovered that I can run a sql script that will populate my parameter @xmlDoc without needing to call sp_xml_preparedocument, and that I can actually pass the @xmlDoc to my stored proc.

  Below is the sql that prepares the data in xml:

  DECLARE @xmlDoc XML

  SET @xmlDoc = (

  SELECT

  au_id, au_fname, au_lname

  FROM

  pubs.dbo.authors

  FOR

  XML RAW('itemlevel'), TYPE, ROOT ('rootlevel'), ELEMENTS XSINIL

  )

  EXEC dbo.show_xml @xmlDoc

  and here is the stored proc that converts the xml into table format:

  ALTER PROC dbo.show_xml

  (

  @xmlDoc XML

  )

  AS

  begin

  SELECT M.Item.query('./au_id').value('.','varchar(20)') au_id,

  M.Item.query('./au_lname').value('.','VARCHAR(250)') au_lname,

  M.Item.query('./au_fname').value('.','varchar(250)') au_fname,

  FROM @xmldoc.nodes('/rootlevel/itemlevel') AS M(Item)

  end

  The result set of the stored proc returns just fine.

  So, two questions:

  1) is sp_xml_preparedocument really necessary? when is it required?

  2) by not calling sp_xml_preparedocument, am I consuming sql server's memory by having it generate an xml doc and not freeing the memory by having not called sp_xml_removedocument ?

  Again, I'm new to xml. Worked with SQL for years, but haven't needed to deal with xml.

  Thanks in advance,

  Pete

• rjv_rnjn

  Hall of Fame

  Points: 3023

  More actions

  October 29, 2009 at 8:27 am

  #1072480

  sp_xml_preparedocument & remove document are required if you are passing the XML string as varchar type. Otherwise you don't need to. I'm not sure but I would doubt if SQL 2k5 would internally call sp_xml_preparedocument for XML data type variables.

  A bit more explanation on BOL