sql sp_xml_preparedocument 函数运用实例

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

sql sp_xml_preparedocument 函数运用实例

标签：