loop with OPENXML

  • So I have my XML doc with whole bunch of data. For this specific INSERT I just need few records. So I try:

    DECLARE @number int

    SET @number = 1

    WHILE @number < 8
    BEGIN
    INSERT INTO SomeTable
    (
    UserID,
    SomeColumn,
    SomeOtherColumn,
    SomeThirdColumn
    )
    SELECT
    UserID,
    SomeColumn,
    SomeOtherColumn,
    SomeThirdColumn
    FROM OPENXML(@intDoc,'/DATA/ITEM')
    WITH (
    UserIDint@user-id,

    SomeColumnvarchar(100)'Paper' + @number + 'Sheet',

    SomeOtherColumnvarchar(50)'Block' + @number + 'Num',

    SomeThirdColumnvarchar(50)'License' + @number + 'Valid'

    )

    SET @number = @number + 1

    END

    The reason to this as there is N amount of columns in my XML doc that go like this:

    Paper1Sheet

    Block1Num

    License1Valid

    Paper2Sheet

    Block2Num

    License2Valid

    Paper3Sheet

    Block3Num

    License3Valid

    ...

    PaperNNSheet

    BlocNNNum

    LicenseNNValid

    So I'm trying to insert them all into a table regardless of how many there are.

    PS: Btw, it doesn't like @user-id inserted like that either.

    Hope someone can help me resolve this.

  • just run this example in query analyzer. hope this will help you to proceed with your specifics

    -- Start Code

    declare @doc nvarchar(4000)

    set @doc =

    '

    <data>

    <paper1sheet>value for paper1sheet</paper1sheet>, <block1num>value for block1num</block1num>, <license1valid>value for license1valid</license1valid>

    <paper2sheet>value for paper2sheet</paper2sheet>, <block2num>value for block2num</block2num>, <license2valid>value for license2valid</license2valid>

    <paper3sheet>value for paper3sheet</paper3sheet>, <block3num>value for block3num</block3num>, <license3valid>value for license3valid</license3valid>

    <paper4sheet a="11">value for paper4sheet</paper4sheet>, <block4num>value for block4num</block4num>, <license4valid>value for license4valid</license4valid>

    <paper5sheet a="12">value for paper5sheet</paper5sheet>, <block5num>value for block5num</block5num>, <license5valid>value for license5valid</license5valid>

    <paper6sheet>value for paper6sheet</paper6sheet>, <block6num>value for block6num</block6num>, <license6valid>value for license6valid</license6valid>

    <paper7sheet>value for paper7sheet</paper7sheet>, <block7num>value for block7num</block7num>, <license7valid>value for license7valid</license7valid>

    </data>

    '

    declare @xml table(columnname varchar(100), columnvalue varchar(1000))

    declare @dochandle integer

    exec sp_xml_preparedocument @dochandle output, @doc

     insert into @xml

     select  

      columnname,

      columnvalue

     from openxml(@dochandle,'/data/*')

      with (

      columnname varchar(8000) '@mp:localname' ,

      columnvalue varchar(8000) './text()'

     &nbsp as t1

    exec sp_xml_removedocument @dochandle  

    select  

     replace(replace(t1.columnname,'paper',''),'sheet','') as id,

     t1.columnvalue as column1,

     t2.columnvalue as column2,

     t3.columnvalue as column3

    from

     @xml as t1

    join

     @xml as t2

    on

     replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t2.columnname,'block',''),'num','')

    and

     isnumeric(replace(replace(t2.columnname,'block',''),'num','')) = 1

    join

     @xml as t3

    on

     replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t3.columnname,'license',''),'valid','')

    and

     isnumeric(replace(replace(t3.columnname,'license',''),'valid','')) = 1

    where

     isnumeric(replace(replace(t1.columnname,'paper',''),'sheet','')) = 1

    -- End Code

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply