Joseph Michael Pesch
VP Programming

SQL Read XML to Generate Table Inserts

by 12. January 2011 10:41

set nocount on

 

-- Sample XML

declare @xmlData XML, @xpath varchar(250)

set @xmlData =

'

<xml>

  <schema>

    <table id="Wholesale.EmailSignUp" schema="Wholesale" name="EmailSignUp">

      <col id="EmailAddress" type="varchar" length="150" nullable="false" quote="''" />

      <col id="CompanyName" type="varchar" length="150" nullable="true" quote="''" />

      <col id="FirstName" type="varchar" length="25" nullable="true" quote="''" />

      <col id="LastName" type="varchar" length="25" nullable="true" quote="''" />

      <col id="PositionAtCompany" type="varchar" length="100" nullable="true" quote="''" />

      <col id="EntryDate" type="datetime" length="25" nullable="false" quote="''" />

      <col id="UpdateDate" type="datetime" length="25" nullable="false" quote="''" />

    </table>

    <table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">

      <col id="ProductInfoId" type="uniquidentifier" length="" nullable="false" quote="''" />

      <col id="FieldName" type="varchar" length="50" nullable="true" quote="''" />

      <col id="DisplayText" type="varchar" length="100" nullable="true" quote="''" />

      <col id="Value" type="int" length="" nullable="true" quote="" />

    </table>

  </schema>

  <data>

    <table id="Wholesale.EmailSignUp">

      <row id="1">

        <col id="EmailAddress" value="joe.pesch@impaccompanies.com" />

        <col id="CompanyName" value="Impac" />

        <col id="FirstName" value="Joe" />

        <col id="LastName" value="Pesch" />

        <col id="PositionAtCompany" value="VP Software Development" />

        <col id="EntryDate" value="1/1/2011 7:00AM" />

        <col id="UpdateDate" value="1/1/2011 7:00AM" />

      </row>   

      <row id="2">

        <col id="EmailAddress" value="jpesch@impaccompanies.com" />

        <col id="CompanyName" value="Impac" />

        <col id="FirstName" value="Joe" />

        <col id="LastName" value="Pesch" />

        <col id="PositionAtCompany" value="VP Software Development" />

        <col id="EntryDate" value="1/1/2011 8:00AM" />

        <col id="UpdateDate" value="1/1/2011 8:00AM" />

      </row>   

    </table>

    <table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">

      <row id="1">

        <col id="ProductInfoId" value="EEC326F3-C445-4B44-8C42-4E4FF84FDE1E" />

        <col id="FieldName" value="TESTING" />

        <col id="DisplayText" value="This is a test" />

        <col id="Value" value="100" />

      </row>   

      <row id="2">

        <col id="ProductInfoId" value="3F5F1FB8-5023-4FCD-ACF4-2B7CD25E6043" />

        <col id="FieldName" value="TESTING_ANOTHER" />

        <col id="DisplayText" value="This is another test" />

        <col id="Value" value="200" />

      </row>   

    </table>

  </data>

</xml>

'

 

-- Temp table for inserts

create table #inserts(id varchar(100), data text)

declare @handle int, @datalen int, @txtptr binary(16)

 

-- Variables for processing

declare @tbl varchar(150), @col varchar(50), @comma varchar(1), @value varchar(max)

 

-- Get handle to XML document

exec sp_xml_PrepareDocument@handle output, @xmlData

 

-- Iterate each table in the XML

declare cur cursor for select id from openxml(@handle, '/xml/schema/table', 1) with (id varchar(50))

open cur

while 'For each table in XML' <> '' begin

  fetch next from cur into @tbl

  if @@fetch_status <> 0 break

  if 'Generate the template record' <> '' begin

    set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'

    insert into #inserts values(@tbl + '.0', 'insert into ' + @tbl + '(')

    set @comma = ''

    set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col'

    declare @id varchar(50), @type varchar(50), @length varchar(50), @nullable varchar(50), @quote varchar(50)

    declare curX cursor for

      select [id], [type], [length], [nullable], [quote]

      from openxml(@handle, @xpath, 1)

      with ([id] varchar(50), [type] varchar(50), [length] varchar(50), [nullable] varchar(50), [quote] varchar(50))

    open curX

    while 'Process each column' <> '' begin

      fetch next from curX into @id, @type, @length, @nullable, @quote

      if @@fetch_status <> 0 break

      set @col = @comma + @id

      select @datalen = datalength(data) from #inserts where id = @tbl + '.0'

      select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'

      updatetext #inserts.data @txtptr @datalen 0 @col

      set @comma = ', '

    end

    select @datalen = datalength(data) from #inserts where id = @tbl + '.0'

    select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'

    updatetext #inserts.data @txtptr @datalen 0 ') values('

    close curX

    deallocate curX

  end

  if 'Generate the values using template' <> '' begin

    set @comma = ''

    set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'

    declare curX cursor for

      select [id] from openxml(@handle, @xpath, 1) with ([id] varchar(50))

    open curX

    while 'Process each row' <> '' begin

      fetch next from curX into @id

      if @@fetch_status <> 0 break

      insert #inserts select @tbl + '.' + @id, data from #inserts where id = @tbl + '.0'

      set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row[@id=' + @id + ']/col'

      declare curY cursor for select [id], [value] from openxml(@handle, @xpath, 1) with ([id] varchar(50), [value] varchar(max))

      open curY

      set @comma = ''

      while 'Process each column' <> '' begin

        fetch next from curY into @col, @value

        if @@fetch_status <> 0 break

        set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col[@id=''' + @col + ''']'

        select @quote = [quote]

        from openxml(@handle, @xpath, 1) with ([quote] varchar(10))

        set @value = @comma + @quote + replace(@value, '''', '''''') + @quote

        select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id

        select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id

        updatetext #inserts.data @txtptr @datalen 0 @value

        set @comma = ','

      end

      close curY

      deallocate curY

      select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id

      select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id

      updatetext #inserts.data @txtptr @datalen 0 ')'

    end

    close curX

    deallocate curX

    delete from #inserts where id = @tbl + '.0'

  end

end

close cur

deallocate cur

 

select * from #inserts

drop table #inserts

 

Tags:

Comments are closed