在OPENXML ()行集的列和 XML 属性之间的映射

    技术2022-05-12  1

     

    OPENXML ()行集的列和 XML 属性之间的映射

     

    --测试数据

    DECLARE @pFileters VARCHAR(max)

    SET @pFileters =

    ' <CountryId>

        <Country _Id="AFG" Status="1" >Afghanistan </Country>

    </CountryId>'

     

    --1

    DECLARE @idoc INT

    EXEC sp_xml_preparedocument @idoc OUTPUT, @pFileters       

    SELECT *

    FROM OPENXML (@idoc, '/CountryId/Country',2)

    with  (a varchar(50)   './@_Id',b varchar(50) './@Status',C varchar(50) '.'   )

     

    --2

    SELECT *

    FROM OPENXML (@idoc, '/CountryId/Country',1)

    with  (_Id varchar(50),Status varchar(50),C varchar(50) '.'   )  

     

     

      

    EXEC sp_xml_removedocument @idoc

     

    --3

    DECLARE @p xml

    SET @p =

    ' <CountryId>

        <Country _Id="AFG" Status="1" >Afghanistan </Country>

    </CountryId>'

    SELECT

        A.x.value('@_Id','varchar(10)') AS a,

        A.x.value('@Status','int') AS b,

        A.x.value('.','varchar(20)') AS C

    FROM @p.nodes('//Country') AS A(x)

    --结果

    /*

    a                                 b                                 C

    ------------------------- --------------------- ------------------

    AFG                             1                      Afghanistan

     

    _Id                              Status                          C

    -------------------- ------------------------- --------------

    AFG                         1                    Afghanistan

     

    a          b           C

    ---------- ----------- --------------------

    AFG        1           Afghanistan

     

    */


    最新回复(0)