Reading an XML file from inside a stored procedure.

Xenon, thanks for the suggestion, but what I need to know is how to read XML from a file.

I'm not sure how I could accomplish what I'm after with DTS as I've got XML files being emailed in, and I'm intending to use xp_readMail etc to collect them. They have xml attachments which I want to parse and store some of the data in the file.

Is it possible to start a DTS package from a stored procedure?
Is it possible to then have a file data source connection in that DTS package dynamically set the path to the path of the attachment.

Thanks,

Dawson

What I sugested was to import that XML file in a table field.. then from there put in a variable, and from that variable using the opnexml sugested above you can browse it in a user friendly way....

Using bcp or DTS you can import the XML file into a table with no transformation at all...

From a stored procedure you can start a job (with exec msdb.dbo.sp_start_job @job_name='XXX').
A DTS after you created it you can schedule it in EM by right click + schedule package)

Regarding dynamicaly setting the path..... I realy don't know if it's possible.......... :((

good luck,
           xenon

What I sugested was to import that XML file in a table field.. then from there put in a variable, and from that variable using the opnexml sugested above you can browse it in a user friendly way....

Using bcp or DTS you can import the XML file into a table with no transformation at all...

From a stored procedure you can start a job (with exec msdb.dbo.sp_start_job @job_name='XXX').
A DTS after you created it you can schedule it in EM by right click + schedule package)

Regarding dynamicaly setting the path..... I realy don't know if it's possible.......... :((

good luck,
           xenon

Thanks for your help people,

In the end, a couple of factors have conspired together to mean that I'm not going to use the xp_***Mail stored procedures to get the attachments. I'll do it in VB.
It turns out that the size of the XML files is around the 25,000 character mark, which means I can't bring it in to a VarChar(8000), but would have to use a TEXT data field in a table. From what I read, it seems that sp_xml_preparedocument would happily take a TEXT parameter, but I'm not sure how to do that.... but this doesn't matter anyhow, as it turns out the XML file is not particularly well formed, and therefore doesn't appear to be able to be easily machine read in it's current form, which is why I'm going to use VB to massage the xml file first, then send the harvested data into the database.


Mr Mr_Peerapol, I'm giving you the points as your suggestion of using the FileSystemObject would have worked, if I'd realised that the xml file was unicode, I discovered this when I ran the code shown on the following link, which worked fantastic straight out of the box
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=359&lngWId=5
I could of course modify the FSO code to work with a unicode file, but by the time I'd realised that that was why it wasn't working, I had already decided not to do it that way anyhow.

Thanks for your help guys.

D.Maniac

Dear D.Maniac,

I also have a need to read an XML file from within a stored procedure.

Combining the ideas of Joseph Gama (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp) and Umachandar Jayachandran ( http://www.sqlxml.org/faqs.aspx?faq=42), I have built the following procedure (u_xmlfile_preparedocument), that currently handles files up to 2.5MB (it can easily be extended to handle larger files if necessary).


Regards, MurrayJohn

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[u_xmlfile_preparedocument]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  execute('CREATE PROCEDURE [dbo].[u_xmlfile_preparedocument] As Begin Return End')
GO

ALTER PROCEDURE dbo.u_xmlfile_preparedocument @hdoc Integer Output,
                                              @xmlfile varchar(1000) = NULL
AS
--
--    Description:
--
--        Utility procedure to invoke sp_xml_preparedocument from a file rather than text
--
--    Credits:
--
--        Joseph Gama (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)
--        Umachandar Jayachandran (http://www.sqlxml.org/faqs.aspx?faq=42)
--
--    Example invocation:
--
--        exec dbo.u_xmlfile_preparedocument @hdoc OUTPUT, 'C:\test.xml'
--
--    Change History
--    
--    Date        Who              Change
--    ----------- ---------------- -------------------------------------------------
--    17-Oct-2003 Murray Crosswell CR841: Initial version
--
--  $Id: dbo.u_xmlfile_preparedocument.PRC,v 1.1 2003/10/21 07:31:04 mcrosswell Exp $
--
Begin

SET NOCOUNT ON

Declare @i int,
        @size int,
        @blocks int,
        @cmdDS varchar(8000),
        @cmdD1 varchar(8000),
        @cmdD2 varchar(8000),
      @cmdD3 varchar(8000),
        @cmdD4 varchar(8000),
        @cmdDF varchar(8000),
        @cmdR1 varchar(8000),
        @cmdR2 varchar(8000),
      @cmdR3 varchar(8000),
        @cmdR4 varchar(8000),
        @cmdF1 varchar(8000),
        @cmdF2 varchar(8000),
      @cmdF3 varchar(8000),
        @cmdF4 varchar(8000),
        @cmdE varchar(8000)

create table #filedetails
(altname varchar(30),size int,createdate varchar(32),
 createtime varchar(32),lastwrittendt varchar(30),
 lastwrittentime varchar(32),lastaccessdt varchar(30),
 lastaccesstime varchar(32),attributes int)

insert into #filedetails exec master..xp_getfiledetails @xmlfile

set @size=(select size from #filedetails)

drop table #filedetails

SET @blocks=@size/4000+1

--create all temporary variables
SET @cmdDS='DECLARE @fso int, @fl int, @hr int, @qm char(1), @vp varchar(8000), @vs varchar(8000), '

SET @cmdDF=' Set @qm = char(39)'
SET @cmdDF=@cmdDF+' Set @vp = ''DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, ''+@qm'
SET @cmdDF=@cmdDF+' Set @vs = @qm+'' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'''
SET @cmdDF=@cmdDF+' EXEC @hr = sp_OACreate ''Scripting.FileSystemObject'', @fso OUT '
SET @cmdDF=@cmdDF+'exec sp_oamethod @fso, ''opentextfile'', @fl out, '''+@xmlfile+''', 1 '

--read and fix data
SET @cmdD1 = ''
SET @cmdD2 = ''
SET @cmdD3 = ''
SET @cmdD4 = ''
SET @cmdR1 = ''
SET @cmdR2 = ''
SET @cmdR3 = ''
SET @cmdR4 = ''
SET @cmdF1 = ''
SET @cmdF2 = ''
SET @cmdF3 = ''
SET @cmdF4 = ''
SET @i=1
WHILE @i<=@blocks
      BEGIN
      IF @i < 171
            BEGIN
                     SET @cmdD1=@cmdD1+'@v'+CONVERT(VARCHAR(9),@i)+' varchar(8000), '
            SET @cmdR1=@cmdR1+'exec @hr=sp_oamethod @fl,''read'','+'@v'+CONVERT(VARCHAR(9),@i)+' out,4000 '
            SET @cmdF1=@cmdF1+'Set @v'+CONVERT(VARCHAR(9),@i)+'=Replace(@v'+CONVERT(VARCHAR(9),@i)+',@qm,@qm+@qm) '      
            END
      ELSE IF @i < 341
            BEGIN
            SET @cmdD2=@cmdD2+'@v'+CONVERT(VARCHAR(9),@i)+' varchar(8000), '
            SET @cmdR2=@cmdR2+'exec @hr=sp_oamethod @fl,''read'','+'@v'+CONVERT(VARCHAR(9),@i)+' out,4000 '
            SET @cmdF2=@cmdF2+'Set @v'+CONVERT(VARCHAR(9),@i)+'=Replace(@v'+CONVERT(VARCHAR(9),@i)+',@qm,@qm+@qm) '
            END
      ELSE IF @i < 511
            BEGIN
                     SET @cmdD3=@cmdD3+'@v'+CONVERT(VARCHAR(9),@i)+' varchar(8000), '
            SET @cmdR3=@cmdR3+'exec @hr=sp_oamethod @fl,''read'','+'@v'+CONVERT(VARCHAR(9),@i)+' out,4000 '
            SET @cmdF3=@cmdF3+'Set @v'+CONVERT(VARCHAR(9),@i)+'=Replace(@v'+CONVERT(VARCHAR(9),@i)+',@qm,@qm+@qm) '      
            END
      ELSE
            BEGIN
            SET @cmdD4=@cmdD4+'@v'+CONVERT(VARCHAR(9),@i)+' varchar(8000), '
            SET @cmdR4=@cmdR4+'exec @hr=sp_oamethod @fl,''read'','+'@v'+CONVERT(VARCHAR(9),@i)+' out,4000 '
            SET @cmdF4=@cmdF4+'Set @v'+CONVERT(VARCHAR(9),@i)+'=Replace(@v'+CONVERT(VARCHAR(9),@i)+',@qm,@qm+@qm) '
            END
        SET @i=@i+1
      END      
IF @cmdD4 <> ''
      SET @cmdD4=LEFT(@cmdD4,len(@cmdD4)-1)
ELSE IF @cmdD3 <> ''
      SET @cmdD3=LEFT(@cmdD3,len(@cmdD3)-1)
ELSE IF @cmdD2 <> ''
      SET @cmdD2=LEFT(@cmdD2,len(@cmdD2)-1)
ELSE
      SET @cmdD1=LEFT(@cmdD1,len(@cmdD1)-1)
--execute sp_xml_preparedocument
SET @cmdE='exec(@vp+'
SET @i=1
WHILE @i<=@blocks
      BEGIN
      SET @cmdE=@cmdE+'@v'+CONVERT(NVARCHAR(9),@i)+'+'
      SET @i=@i+1
      END      
SET @cmdE=LEFT(@cmdE,len(@cmdE)-1)
SET @cmdE=@cmdE+'+@vs) '
SET @cmdE=@cmdE+'EXEC @hr = sp_OADestroy @fl EXEC @hr = sp_OADestroy @fso'
exec( @cmdDS+@cmdD1+@cmdD2+@cmdD3+@cmdD4+@cmdDF+@cmdR1+@cmdR2+@cmdR3+@cmdR4+@cmdF1+@cmdF2+@cmdF3+@cmdF4+@cmdE )

OPEN GLOBAL he_cur
FETCH he_cur INTO @hdoc
DEALLOCATE GLOBAL he_cur

IF @hdoc IS NULL
    RAISERROR( 'Invalid Handle!', 16, 1 )

END

MurrayJohn,

You did a great job on that stored procedure, it works perfect.
The only problem I have is because it handles files only up to 2.5 MB and I need to handle files up to 10 MB.
I remember you said it could easily be extended to handle larger files if necessary....:)  Well, I gave it a look
but I got completely lost. Could you help me on that one ?

Thanks a lot

Ceta

Ceta,

to make the procedure handle larger files one would just add more set of variables (i.e. @cmdD5, @cmdR5, @cmdF5) and extend the code that utilises them.

Alternatively one could abstract the procedure one more level so that the above variable extensions are generated as well.

I have now done this below - the new version should now easily support 10MB files.

The new limit (I think) is around 14MB. To go beyond there, the variable @cmdsrc2 in the following procedure would need to be split into multiple parts as it will be the first one to hit the 8,000 character limit.


Cheers, MurrayJohn.


if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[u_xmlfile_preparedocument]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  execute('CREATE PROCEDURE [dbo].[u_xmlfile_preparedocument] As Begin Return End')
GO

ALTER PROCEDURE dbo.u_xmlfile_preparedocument @hdoc Integer Output,
                                              @xmlfile varchar(1000) = NULL
AS
--
--    Description:
--
--        Utility procedure to invoke sp_xml_preparedocument from a file rather than text
--
--    Credits:
--
--        Joseph Gama (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)
--        Umachandar Jayachandran (http://www.sqlxml.org/faqs.aspx?faq=42)
--
--    Example invocation:
--
--        exec dbo.u_xmlfile_preparedocument @hdoc OUTPUT, 'C:\test.xml'
--
--    Change History
--    
--    Date        Who              Change
--    ----------- ---------------- -------------------------------------------------
--    17-Oct-2003 Murray Crosswell CR841: Initial version
--    24-Nov-2003 Murray Crosswell CR841: Move to another level of code generation abstraction to support larger files.
--
--  $Id: dbo.u_xmlfile_preparedocument.PRC,v 1.2 2003/10/22 00:12:38 mcrosswell Exp $
--
Begin

SET NOCOUNT ON    

Declare @i int,
        @size int,
        @blocks int,
        @varcnt int,
        @qm char(1),
        @bperv integer,
        @cmdsrc varchar(8000),
        @cmdsrc2 varchar(8000),
        @cmdsrc3 varchar(8000),
        @CRLF Varchar(2)

Set @CRLF = Char(13) + Char(10)

create table #filedetails
(altname varchar(30),size int,createdate varchar(32),
 createtime varchar(32),lastwrittendt varchar(30),
 lastwrittentime varchar(32),lastaccessdt varchar(30),
 lastaccesstime varchar(32),attributes int)

insert into #filedetails exec master..xp_getfiledetails @xmlfile

set @size=(select size from #filedetails)

drop table #filedetails

Set @bperv = 165
Set @qm = char(39)
Set @blocks=@size/4000+1
Set @varcnt=(@blocks-1)/@bperv+1

Set @cmdsrc = 'Declare @cmdDS varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdES varchar(8000), '
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc = @cmdsrc + '@cmdD' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdR' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdF' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdE' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @i=@i+1
  End      
Set @cmdsrc = @cmdsrc + '@cmdDF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdEF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@i int, @blocks int'

Set @cmdsrc = @cmdsrc + ' SET @blocks=' + Convert(Varchar(9),@blocks)
Set @cmdsrc = @cmdsrc + ' SET @cmdDS='+@qm+'DECLARE @fso int, @fl int, @hr int, @qm char(1), @vp varchar(8000), @vs varchar(8000), '+@qm

Set @cmdsrc = @cmdsrc + ' SET @cmdDF='+@qm+' Set @qm = char(39)'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vp = '+@qm+@qm+'DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, '+@qm+@qm+'+@qm'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vs = @qm+'+@qm+@qm+' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'+@qm+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' EXEC @hr = sp_OACreate '+@qm+@qm+'Scripting.FileSystemObject'+@qm+@qm+', @fso OUT '+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+'exec sp_oamethod @fso, '+@qm+@qm+'opentextfile'+@qm+@qm+', @fl out, '+@qm+@qm+@xmlfile+@qm+@qm+', 1 '+@qm
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc = @cmdsrc + ' SET @cmdD' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdR' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdF' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdE' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @i=@i+1
  End      
Set @cmdsrc = @cmdsrc + ' SET @i=1'
Set @cmdsrc = @cmdsrc + ' WHILE @i<=@blocks'
Set @cmdsrc = @cmdsrc + ' BEGIN'
Set @cmdsrc2 = ''
Set @i=1
While @i<=@varcnt
  Begin
  If @i <> 1 Set @cmdsrc2 = @cmdsrc2 + ' ELSE'
  If @i <> @varcnt Set @cmdsrc2 = @cmdsrc2 + ' IF @i < ' + Convert(Varchar(9),@i*@bperv+1)
  If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' BEGIN'  
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdD' + Convert(Varchar(9),@i) + '=@cmdD' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' varchar(8000), '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdR' + Convert(Varchar(9),@i) + '=@cmdR' + Convert(Varchar(9),@i) + '+'+@qm+'exec @hr=sp_oamethod @fl,'+@qm+@qm+'read'+@qm+@qm+','+@qm+'+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' out,4000 '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdF' + Convert(Varchar(9),@i) + '=@cmdF' + Convert(Varchar(9),@i) + '+'+@qm+'Set @v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'=Replace(@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+',@qm,@qm+@qm) '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdE' + Convert(Varchar(9),@i) + '=@cmdE' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'+'+@qm
  If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' END'
  Set @i=@i+1
  End      
Set @cmdsrc3 = ' SET @i=@i+1'
Set @cmdsrc3 = @cmdsrc3 + ' END'

Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdD' + Convert(Varchar(9),@varcnt) + '=LEFT(@cmdD' + Convert(Varchar(9),@varcnt) + ',len(@cmdD' + Convert(Varchar(9),@varcnt) + ')-1)'

Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdES='+@qm+'exec(@vp+'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdEF='+@qm+'@vs) EXEC @hr = sp_OADestroy @fl EXEC @hr = sp_OADestroy @fso'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' exec( @cmdDS+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdD' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End      
Set @cmdsrc3 = @cmdsrc3 + '@cmdDF+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdR' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End      
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdF' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End      
Set @cmdsrc3 = @cmdsrc3 + '@cmdES+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdE' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End      
Set @cmdsrc3 = @cmdsrc3 + '@cmdEF )'

Execute (@cmdsrc+@cmdsrc2+@cmdsrc3)

OPEN GLOBAL he_cur
FETCH he_cur INTO @hdoc
DEALLOCATE GLOBAL he_cur

IF @hdoc IS NULL
    RAISERROR( 'Invalid Handle!', 16, 1 )

End

You are number one !
Perfect....
Thank you very much...

I  need to use the namespace with this store procedure as I am trying to read the file which is created by ADO

'<xml xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'  

is the namespace.

How should we do it ?

Rahul

>>How should we do it ?<<
You post a new question in this Topic Area.

Rahul,

one just needs to add another optional parameter to the procedure and then pass the value to sp_xml_preparedocument as follows:

Replace:

ALTER PROCEDURE dbo.u_xmlfile_preparedocument @hdoc Integer Output,
                                              @xmlfile varchar(1000) = NULL
AS

With:

ALTER PROCEDURE dbo.u_xmlfile_preparedocument @hdoc Integer Output,
                                              @xmlfile varchar(1000) = NULL,
                                              @xpath_namespaces varchar(1000) = NULL
AS

... and replace:

Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vp = '+@qm+@qm+'DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, '+@qm+@qm+'+@qm'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vs = @qm+'+@qm+@qm+' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'+@qm+@qm+@qm

With:

Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vp = '+@qm+@qm+'DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, '+@qm+@qm+'+@qm'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vs = @qm+'+@qm+@qm+', '+@qm+@qm+'+@qm+'+@qm+@qm+IsNull(@xpath_namespaces,'')+@qm+@qm+'+@qm+'+@qm+@qm+' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'+@qm+@qm+@qm


Now procedure u_xmlfile_preparedocument supports the complete functionality of sp_xml_preparedocument.


Cheers, MurrayJohn.

Thanx a lot
Rahul

John Murray,

Can you take your solution a step farther?
I am trying to write one row into a table for each XML tag "TRANSACTION" that is in my XML file, of which there are multiples.  Within each TRANSACTION, there are the various levels of elements.  When I do multiple OPENXML statements to get the various pieces of information to load into a single table row, I see multiple rows appearing in Query Analyzer for each OPENXML statement.

How should I code these OPENXML statements so as to make sure that I insert one row per TRANSACTION tag?

I've included my stored procedure (as follows):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE pr_NIPR_InsertResponse
                  (@xmlDocName text)

AS

      declare @intDoc int
      declare @xmlDoc2 varchar(8000)

-- SAMPLE DATA
--<?xml version="1.0" standalone="yes"?>
--<NIPR>
--    <TRANSACTION>
--      <APPLICANT>
--            <GENDER>M</GENDER>
--            <LICENSE_KEY>
--                  <LICENSE_CLASS>1022</LICENSE_CLASS>
--                  <LICENSE_ID>ABCDEFGH</LICENSE_ID>
--            </LICENSE_KEY>
--            <TAX_NUM>123456789</TAX_NUM>
--            <TYPE>I</TYPE>
--      </APPLICANT>
--      <CUST_BATCH>5</CUST_BATCH>
--      <CUST_TRANS>1</CUST_TRANS>
--      <LICENSE>
--            <DATE_EFFECTIVE>20021017</DATE_EFFECTIVE>
--            <INT_RESPONSE>
--                  <DATE_STAMP>20040730</DATE_STAMP>
--                  <RESP_MESSAGE>
--                        <ACTION_REQD>N</ACTION_REQD>
--                        <COMMENTS>Transaction has been sent to state for review</COMMENTS>
--                        <COMMENT_CODE>10003</COMMENT_CODE>
--                  </RESP_MESSAGE>
--                  <TIME_STAMP>092937000</TIME_STAMP>
--            </INT_RESPONSE>
--            <LICENSE_CLASS>1022</LICENSE_CLASS>
--            <LOA>
--                  <LOA_CODE>11</LOA_CODE>
--            </LOA>
--            <LOA>
--                  <LOA_CODE>30</LOA_CODE>
--            </LOA>
--            <LOA>
--                  <LOA_CODE>127</LOA_CODE>
--            </LOA>
--      </LICENSE>
--      <NIPR_TRANS>16793274</NIPR_TRANS>
--      <TRANS_HEAD>
--            <DATE_STAMP>20040730</DATE_STAMP>
--            <STATE_CODE>ID</STATE_CODE>
--            <TIME_STAMP>103224000</TIME_STAMP>
--            <TRANSACTION_TYPE>1</TRANSACTION_TYPE>
--      </TRANS_HEAD>
--      <TRANS_STATUS>SR</TRANS_STATUS>
--      <VALID>P</VALID>
--   </TRANSACTION>
--</NIPR>

      -- load XML document into memory -- called to obtain a document handle that is then passed to the OPENXML statement
      EXEC u_xmlfile_preparedocument @intDoc OUTPUT, @xmlDocName      -- worked passing "C:\DotNet Apps\NIPRApp\bin\LGI200407301031_NIPR.XM" to procedure

      -- process XML document with OPENXML statement
            -- 2 indicates an element-centric mapping (instead of by attributes)
      
      -- Transaction batch info
      SELECT *
            FROM OPENXML(@intDoc, '/NIPR/TRANSACTION', 2)
            WITH      (VALID char(1),
                  CUST_BATCH int,
                  CUST_TRANS int,
                  NIPR_TRANS int,
                  TRANS_STATUS char(2))
      -- Transaction batch info (more)
      SELECT *
            FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/TRANS_HEAD', 2)
            WITH       (DATE_STAMP char(8),
                  TIME_STAMP char(9),
                  STATE_CODE char(2),
                  TRANSACTION_TYPE int)
      -- Transaction Response info
      SELECT *
            FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/LICENSE/INT_RESPONSE/RESP_MESSAGE', 2)
            WITH       (ACTION_REQD char(1),
                  COMMENTS varchar(500),
                  COMMENT_CODE int)
      -- Applicant Tax ID info
      SELECT *
            FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/APPLICANT',2)
            WITH      (TAX_NUM char(9))
      -- Applicant License info
      SELECT *
            FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/APPLICANT/LICENSE_KEY',2)
            WITH      (LICENSE_CLASS int,
                  LICENSE_ID varchar(14))

               -- INSERT data into 'NIPRResponse' table
               -- ???????

      -- remove XML document from memory
      EXEC sp_xml_removedocument @intDoc
GO
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thanks for any help you can give on this!
Kevin

Kevin,

your query is not specifically related to my procedure but is a general OpenXML one - regardless the following suggestion is offered:

Within each transaction block there needs to be a unique identifier - lets assume element NIPR_TRANS is going to be unique.

In this case we could expose NIPR_TRANS within each query as follows:

     SELECT *
          FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/TRANS_HEAD', 2)
          WITH      (NIPR_TRANS int '../NIPR_TRANS',
               DATE_STAMP char(8),
               TIME_STAMP char(9),
               STATE_CODE char(2),
               TRANSACTION_TYPE int)
     -- Transaction Response info

     SELECT *
          FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/LICENSE/INT_RESPONSE/RESP_MESSAGE', 2)
          WITH (NIPR_TRANS int '../../../NIPR_TRANS',
               ACTION_REQD char(1),
               COMMENTS varchar(500),
               COMMENT_CODE int)
     -- Applicant Tax ID info

     SELECT *
          FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/APPLICANT',2)
          WITH     (NIPR_TRANS int '../NIPR_TRANS',
                    TAX_NUM char(9))
     -- Applicant License info
     SELECT *
          FROM OPENXML(@intDoc, '/NIPR/TRANSACTION/APPLICANT/LICENSE_KEY',2)
          WITH     (NIPR_TRANS int '../../NIPR_TRANS',
               LICENSE_CLASS int,
               LICENSE_ID varchar(14))


One could then place a cursor around the first main transaction query retrieving the NIPR_TRANS value on each FETCH and then use the value in a 'WHERE' clause added to each of the above secondary select statements.

If there is not a unique transaction reference then you are in trouble (or you need to add one if you have control of the XML creation).

It could be added as an attribute like '<TRANSACTION ID="1">' and then referenced within the WITH clause as:

ID int '@ID',

in the primary transaction query or:

ID int '../@ID'

in the secondary queries (matching the ../ depth as per NIPR_TRANS shown above).


Cheers, Murray.