sql sp_xml_preparedocument 函数运用实例

FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) FROM @t WHERE ID<=LEN(@s+‘a‘) AND CHARINDEX(@split,@split+@s,ID)=ID RETURN END GO View Code 技术分享

Create proc Test
As
Begin
    Declare @data xml
    Declare @idoc int
    Set @data=
    (
        Select  ipduid as ‘@pduid‘ , sConfig.query(‘/items/item‘)  From xxx_BaseInfo
        where sConfig is not null
         FOR xml path,root(‘configinfo‘) 
    )
    --create an internal representation of the xml document.
    exec sp_xml_preparedocument @idoc output, @data
    -- execute a select statement that uses the openxml rowset provider.

    Create table #Tmp
    (
            pduid varchar(50),
            prodLine varchar(50),
            proFamily varchar(50),
            proPdt varchar(50),
            prodNo varchar(50)
    )

Insert Into #Tmp
    Select * From (
            Select pduid ,  (Select col From dbo.f_splitSTR(customerid,‘/‘) Where id=1) As prodLine,
            (Select col From dbo.f_splitSTR(customerid,‘/‘) Where id=2) As prodFamily,
            (Select col From dbo.f_splitSTR(customerid,‘/‘) Where id=3) As prodPdt,
            (Select col From dbo.f_splitSTR(customerid,‘/‘) Where id=4) As prodNo
    From openxml (@idoc, ‘/configinfo/row/item‘,2)
    With (
                pduid  varchar(50)    ‘../@pduid‘ ,
                customerid varchar(500) ‘@id‘
             )
    ) as T


exec sp_xml_removedocument @idoc

Select * from #Tmp

End

View Code

标签：