title: T-SQL - Use of sp_xml_preparedocumet in functions
body: <p>i have a problem with functions (UDF) and system procedures.
  I can't use a procedures in a UDF.
  But is there a workaround for this problem?</p>
  
  <p>I have a view and in the view a will call the function xmlHistory.</p>
  
  <p>In the function i will prepare my XML and then i will return the result.
  But i can't run the function due to due to EXEC sp_xml_preparedocumet.</p>
  
  <p>Is there a other solution to use the procedure sp_xml_preparedocumet?</p>
  
  <pre><code>SELECT 
          irsys.referencenumber AS [Ticket-Nr], 
          dbo.xmlHistory(irsys.caseid,1,'AssignedRole') AS [Role] ...
  
  
  
  CREATE FUNCTION [dbo].[xmlHistory] (@caseID int, @historyID int, @name varChar(50) )
  RETURNS varChar
  
  AS
  BEGIN
      DECLARE @val varChar(50)
      DECLARE @docHandle int
      DECLARE @xmlDocument nvarchar(max) -- or xml type
          SET @xmlDocument = (SELECT [historyitem]
            FROM [HLDATA].[dbo].[hlsyscasehistory]
            where caseid = @caseID and historyid = @historyID)
          EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
          -- Use OPENXML to provide rowset consisting of customer data.
  
          set @val = (SELECT Value 
          FROM OPENXML(@docHandle, 'HistoryDataDto/ODEs/ODE/Attributes/Attribute',2) 
  
          WITH (Name varchar(50), Value varchar(50))
  
          where name = @name)
       RETURN @val
  END
  
  GO
  </code></pre>
  
  <p>Thank you </p>
  
  <p>Felix</p>