Results 1 to 2 of 2

Thread: XML processing

  1. #1
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63

    Unanswered: XML processing

    Hey All,

    I've got a process that creates records in my database based on XML input that I've gotten. What I am doing it giving this XML to a stored procedure to handle a specific task, then modify the XML and send it to the next stored procedure.

    For instance, the XML could hold header records with detail records, I would first send the XML to a stored procedure that creates the header records, then updates the XML so the XML now knows the identity values of the header records I have just created, and then send the XML to the next stored procedure to create the details for those headers.

    All works great and fine, but I have a problem with writing the identity values back to the XML. It seems I can only change one item in the XML at a time and thus need to loop this. For many records this really takes a long time.

    Here is some sample code of what I'm doing (please excuse any typos, this is a simplified version of the code) :
    Code:
      declare @lvSeq numeric(15)
      declare @lvRowNo int
      declare @lvNumRows int
    
      insert into myHeaderTable (
        recid, recdesc
      ) select     
        ref.value('@recid', 'nvarchar(25)') recid,
        ref.value('@recdesc', 'nvarchar(250)') recdesc
      from @pXML.nodes('//headers/header') R(ref)
    
      select @lvRowNo=1, @lvNumRows = @pXML.value('count(//headers/header)', 'int')
      while (@lvRowNo<=@lvNnumRows) begin
        select @lvSeq = recseq 
        from myHeaderTable
        where recid = @pXML.value('//headers/header[position()=sql:variable("@lvRowNo")]/@recid)
    
        set @pXML.modify('replace value of (//headers/header[position()=sql:variable("@lvRowNo")]/@recseq with sql:variable("@lvSeq")')
    
        select @lvRowNo=@lvRowNo+1
      end
    Obviously I am looking for a better way to update the XML with the sequences. The insert takes a second, the loop takes minutes with large XML sets. I guess MSSQL is searching the whole XML to find the item to update.

    It would be nice if I didn't have to loop through the XML. One solution I was thinking off is to store the XML in a temporary table with a single record per header item. Then I could do the modify in one go and recreate the XML by simply selecting the contents of the temporary tabel. I have no idea if this is possible.

    So something like this:
    Code:
      select 
        ref.value('@recid','nvarchar(25)') recid,
        ref.value('.','XML') XMLData -- this gives an error
      into #TMP_XML
      from @pXML.nodes('//headers/header') R(ref)
    
      insert into myHeaderTable (
        recid, recdesc
      ) select     
        recid,
        ref.value('@recdesc', 'nvarchar(250)') recdesc
      from #TMP_XML CROSS APPLY XMLData.nodes('/header') R(ref)
    
      update #TMP_XML
        set XMLData.modify('replace ....')
        from myheadertable
        where #TMP_XML.recid = myheadertable.recid
    
      -- recreate XML here, not sure how....
    Greetz,

    Bastiaan Olij

  2. #2
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    For anyone who is interested in this, I solved it exactly the way I wanted after figuring out how it should work.

    The format of my XML, just looking at my header table is the same as if I do the following:
    Code:
    select *
    from myheadertable
    for xml path('header'), root('headers'), type
    This is enough for the example. It kinda looks like this:
    Code:
    <headers>
      <header>
        <recseq>1</recseq>
        <recid>123456</recid>
        <recdesc>Invoice #123456<recdesc>
      </header>
    </headers>
    You can imagine how multiple invoice headers are put into this XML and how I would later add invoice details to the XML. To create new headers I simply not specify a sequence, the column must exist though.

    The logic will update existing invoices, and create new invoices. Simplified it now looks like this (the actual code checks for the sequence, but the example works better with the invoice id (=number), the sequence is totally ignored):

    Code:
    -- build temporary table with data
    select
      ref.value('./recid[1]', 'nvarchar(25)') recid,
      ref.value('./recdesc[1]', 'nvarchar(250)') recdesc,
      ref.query('./*') XMLdata
    into #TMP_HEADERS
    from @pXML.nodes('//headers/header')
    
    -- create new headers
    insert into myHeaderTable (
      recid, recdesc
    ) select
      recid, recdesc
    from #TMP_HEADERS tmp
    where not exists (select 1 from myHeaderTable where recid=tmp.recid)
    
    -- get sequences of new and existing records and put them into the XML
    update #TMP_HEADERS
      set XMLData.modify('replace value of (//recseq[1]/text()[1] with sql:column("recseq")')
      from myHeaderTable
      where myHeaderTable.recid = #TMP_HEADERS.recid
    
    -- select the result back into our (output) parameter
    select @pXML = (
      select XMLData header
      from #TMP_HEADERS
      for xml PATH('headers'), type
    )
    Now I can give the XML to my create details stored procedure and it will use the header sequence to join the details with (this is why I put the XML as is in my temporary table instead of rebuilding the XML from my invoice header table. I do not know what other data is in there)
    Greetz,

    Bastiaan Olij

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •