Example of using SQL Sp_xml_preparedocument function

Table structure:

CREATE FUNCTIONF_splitstr (@s   varchar(8000),--string to be split@split varchar(Ten)--Data Separators)RETURNS @re TABLE(colvarchar( -)) asBEGIN --Create a secondary table for split processing (only table variables can be manipulated in a user-defined function) DECLARE @t TABLE(IDint IDENTITYBbit) INSERT @t(b)SELECT TOP 8000 0  fromsyscolumns a,syscolumns bINSERT @re SELECT SUBSTRING(@sIdCHARINDEX(@split,@s+@split, ID)-ID) from @t WHEREId<=LEN(@s+'a')    and CHARINDEX(@split,@split+@s, ID)=IDRETURNENDGO

View Code

Create procTest asBegin    Declare @dataXMLDeclare @idoc int    Set @data=    (        SelectIpduid as '@pduid', Sconfig.query ('/items/item') fromXxx_baseinfowhereSconfig is  not NULL          forXML Path,root ('Configinfo')     )    --Create an internal representation of the XML document.    execsp_xml_preparedocument@idocOutput@data    --execute a SELECT statement that uses the OPENXML rowset provider.    Create Table#Tmp (Pduidvarchar( -), Prodlinevarchar( -), profamilyvarchar( -), PROPDTvarchar( -), Prodnovarchar( -)    )Insert  into#TmpSelect *  from (            SelectPduid, (SelectCol fromDBO.F_SPLITSTR (CustomerID,'/')WhereId=1) asProdline, (SelectCol fromDBO.F_SPLITSTR (CustomerID,'/')WhereId=2) asprodfamily, (SelectCol fromDBO.F_SPLITSTR (CustomerID,'/')WhereId=3) asPRODPDT, (SelectCol fromDBO.F_SPLITSTR (CustomerID,'/')WhereId=4) asProdno fromOPENXML (@idoc,'/configinfo/row/item',2)     with(Pduidvarchar( -)'.. /@pduid', CustomerIDvarchar( -)'@id'             )    )  asTexecSp_xml_removedocument@idocSelect *  from#TmpEnd

View Code

Example of using SQL Sp_xml_preparedocument function