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