Post reply

OpenXML Inserting to 4 Existing Tables

  • June 1, 2005 at 8:26 am

    #186210

    The sproc below does NOT raise an error message in Query Analyzer,

    but it DOES raise an error in the MS Sql 2000 database when I try to save it as a sproc.

    (I'm using the same DB both times)

    Error 207: invalid column name "ImportID".
    I just can't see why it's doing this...I need help.
    I'm shredding an xml document into 4 tables via OpenXML.  I've included the xml file,

    sql scripts for the 4 tables and the sproc that raises the error.

    Help would be much appreciated.
    Thank you,
    Paul
    ###########  XML File   ##########################################################################
    <?xml version="1.0"?>

    <ownershipDocument>

        <schemaVersion>X0202</schemaVersion>
        <documentType>4</documentType>
        <periodOfReport>2005-05-23</periodOfReport>
        <notSubjectToSection16>0</notSubjectToSection16>
        <issuer>

            <issuerCik>0000733269</issuerCik>

            <issuerName>ACXIOM CORP</issuerName>

            <issuerTradingSymbol>ACXM</issuerTradingSymbol>

        </issuer>

        <reportingOwner>

            <reportingOwnerId>

                <rptOwnerCik>0001179677</rptOwnerCik>

                <rptOwnerName>MORGAN CHARLES D</rptOwnerName>

            </reportingOwnerId>

            <reportingOwnerAddress>

                <rptOwnerStreet1>ACXIOM CORPORATION</rptOwnerStreet1>

                <rptOwnerStreet2>1 INFORMATION WAY</rptOwnerStreet2>

                <rptOwnerCity>LITTLE ROCK</rptOwnerCity>

                <rptOwnerState>AR</rptOwnerState>

                <rptOwnerZipCode>72202</rptOwnerZipCode>

                <rptOwnerStateDescription></rptOwnerStateDescription>

            </reportingOwnerAddress>

            <reportingOwnerRelationship>

                <isDirector>1</isDirector>

                <isOfficer>1</isOfficer>

                <isTenPercentOwner>0</isTenPercentOwner>

                <isOther>0</isOther>

                <officerTitle>President/Company Leader</officerTitle>

            </reportingOwnerRelationship>

        </reportingOwner>

        <nonDerivativeTable>

            <nonDerivativeTransaction>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>3714</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>12.405</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>A</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>3022586</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                      &n! bsp; <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </nonDerivativeTransaction>

            <nonDerivativeTransaction>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>27545</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>15.7</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>A</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>3050131</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </nonDerivativeTransaction>

            <nonDerivativeTransaction>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

              &! nbsp; </securityTitle>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>17043</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>11.14</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>A</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>3067174</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </nonDerivativeTransaction>

          &nbs! p; <nonDerivativeTransaction>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>98631</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>16.35</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>A</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>3165805</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                      &n! bsp; <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </nonDerivativeTransaction>

            <nonDerivativeHolding>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>1628</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>I</value>

                    </directOrIndirectOwnership>

                    <natureOfOwnership>

                        <value>by Family Ltd Prtshp</value>

                    </natureOfOwnership>

                </ownershipNature>

            </nonDerivativeHolding>

            <nonDerivativeHolding>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>52370.8694</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>I</value>

                    </directOrIndirectOwnership>

                    <natureOfOwnership>

                        <value>by Managed Account 1</value>

                    </natureOfOwnership>

                </ownershipNature>

            </nonDerivativeHolding>

            <nonDerivativeHolding>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>7386.5261</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>I</value>

                    </directOrIndirectOwnership>

                    <natureOfOwnership>

                        <value>by Managed Account 2</value>

                    </natureOfOwnership>

                </ownershipNature>

            </nonDerivativeHolding>

            <nonDerivativeHolding>

                <securityTitle>

                    <value>Common Stock, $.10 Par Value</value>

                </securityTitle>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>103195</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>I</value>

                    </directOrIndirectOwnership>

                    <natureOfOwnership>

                        <value>by Spouse</value>

                    </natureOfOwnership>

                </ownershipNature>

            </nonDerivativeHolding>

        </nonDerivativeTable>

        <derivativeTable>

            <derivativeTransaction>

                <securityTitle>

                    <value>Non-Qualified Stock Option (right to buy)</value>

                </securityTitle>

                <conversionOrExercisePrice>

                    <value>11.14</value>

                </conversionOrExercisePrice>

                <transactionDate>

                  !   <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>17043</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>0</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>D</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <exerciseDate>

                    <footnoteId id="F1"/>

                </exerciseDate>

                <expirationDate>

                    <value>2016-10-02</value>

                </expirationDate>

                <underlyingSecurity>

                    <underlyingSecurityTitle>

                        <value>Common Stock, $.10 Par Value</value>

                    </underlyingSecurityTitle>

                    <underlyingSecurityShares>

                        <value>17043</value>

                    </underlyingSecurityShares>

                </underlyingSecurity>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>0</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </derivativeTransaction>

            <derivativeTransaction>

                <securityTitle>

                    <value>Non-Qualified Stock Option (right to buy)</value>

                </securityTitle>

                <conversionOrExercisePrice>

                    <value>12.405</value>

                </conversionOrExercisePrice>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>3714</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>0</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>D</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <exerciseDate>

                    <footnoteId id="F2"/>

                </exerciseDate>

                <expirationDate>

                    <value>2006-01-23</value>

                </expirationDate>

                <underlyingSecurity>

                    <underlyingSecurityTitle>

                        <value>Common Stock, $.10 Par Value</value>

                    </underlyingSecurityTitle>

                    <underlyingSecurityShares>

                        <value>3714</value>

                    </underlyingSecurityShares>

                </underlyingSecurity>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>0</value>

                    </sharesOwnedFollowingTransaction>

              ! ;  </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </derivativeTransaction>

            <derivativeTransaction>

                <securityTitle>

                    <value>Non-Qualifi! ed Stock Option (right to buy)</value>

                </securityTitle>

                <conversionOrExercisePrice>

                    <value>15.7</value>

                </conversionOrExercisePrice>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>27545</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>0</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>D</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <exerciseDate>

                    <value>1997-05-28</value>

                </exerciseDate>

                <expirationDate>

                    <value>2012-05-28</value>

                </expirationDate>

                <underlyingSecurity>

                    <underlyingSecurityTitle>

                        <value>Common Stock, $.10 Par Value</value>

                    </underlyingSecurityTitle>

                    <underlyingSecurityShares>

                        <value>27545</value>

                    </underlyingSecurityShares>

                </underlyingSecurity>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>0</value>

                    </sharesOwnedFollowingTransaction>

                </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </derivativeTransaction>

            <derivativeTransaction>

                <securityTitle>

                    <value>Non-Qualified Stock Option (right to buy)</value>

                </securityTitle>

                <conversionOrExercisePrice>

                    <value>16.35</value>

                </conversionOrExercisePrice>

                <transactionDate>

                    <value>2005-05-23</value>

                </transactionDate>

                <transactionCoding>

                    <transactionFormType>4</transactionFormType>

                    <transactionCode>M</transactionCode>

                    <equitySwapInvolved>0</equitySwapInvolved>

                </transactionCoding>

                <transactionAmounts>

                    <transactionShares>

                        <value>98631</value>

                    </transactionShares>

                    <transactionPricePerShare>

                        <value>0</value>

                    </transactionPricePerShare>

                    <transactionAcquiredDisposedCode>

                        <value>D</value>

                    </transactionAcquiredDisposedCode>

                </transactionAmounts>

                <exerciseDate>

                    <footnoteId id="F3"/>

                </exerciseDate>

                <expirationDate>

                    <value>2017-08-07</value>

                </expirationDate>

                <underlyingSecurity>

                    <underlyingSecurityTitle>

                        <value>Common Stock, $.10 Par Value</value>

                    </underlyingSecurityTitle>

                    <underlyingSecurityShares>

                        <value>98631</value>

                    </underlyingSecurityShares>

                </underlyingSecurity>

                <postTransactionAmounts>

                    <sharesOwnedFollowingTransaction>

                        <value>0</value>

                    </sharesOwnedFollowingTransaction>

             &nbs! p;  </postTransactionAmounts>

                <ownershipNature>

                    <directOrIndirectOwnership>

                        <value>D</value>

                    </directOrIndirectOwnership>

                </ownershipNature>

            </derivativeTransaction>

        </derivativeTable>

        <footnotes>

            <footnote id="F1">25% of this option vested on the date of grant.  25% vested on 7/2/02.  The remaining 50% vested on July 16, 2004.  Date of grant is 10/2/01.</footnote>

            <footnote id="F2">This option became fully vested on 1/24/05. Date of grant is 1/24/96.</footnote>

            <footnote id="F3">This option became fully vested on 7/16/04.  Date of grant is 8/7/02.</footnote>

        </footnotes>

        <ownerSignature>

            <signatureName>By: Catherine L. Hughes, Attorney-in-Fact  For: Charles D. Morgan</signatureName>

            <signatureDate>2005-05-25</signatureDate>

        </ownerSignature>

    </ownershipDocument>

    ===================================================================================================
    ###########  SQL Scripts   ##########################################################################
    1)

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Form_004_ownershipDocument]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Form_004_ownershipDocument]

    GO

    CREATE TABLE [dbo].[Form_004_ownershipDocument] (

     [ownershipDocument_ID] [int] IDENTITY (1, 1) NOT NULL ,

     [ownershipDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [schemaVersion] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [documentType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [periodOfReport] [datetime] NULL ,

     [notSubjectToSection16] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [issuerCik] [int] NULL ,

     [issuerName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [issuerTradingSymbol] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerCik] [int] NULL ,

     [rptOwnerName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerStreet1] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerStreet2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerCity] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [rptOwnerZipCode] [int] NULL ,

     [rptOwnerStateDescription] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [isDirector] [int] NULL ,

     [isOfficer] [int] NULL ,

     [isTenPercentOwner] [int] NULL ,

     [isOther] [int] NULL ,

     [officerTitle] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [otherText] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [signatureName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [signatureDate] [datetime] NULL ,

     [ImportID] [int] NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    2)

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Form_004_nonDerivativeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Form_004_nonDerivativeTable]

    GO

    CREATE TABLE [dbo].[Form_004_nonDerivativeTable] (

     [ownershipDocument_ID] [int] NULL ,

     [securityTitle] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [conversionOrExercisePrice] [smallmoney] NULL ,

     [transactionDate] [smalldatetime] NULL ,

     [transactionFormType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [transactionCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [equitySwapInvolved] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [transactionShares] [int] NULL ,

     [transactionPricePerShare] [smallmoney] NULL ,

     [transactionAcquiredDisposedCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [exerciseDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [expirationDate] [smalldatetime] NULL ,

     [underlyingSecurityTitle] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [underlyingSecurityShares] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [sharesOwnedFollowingTransaction] [int] NULL ,

     [directOrIndirectOwnership] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    3)

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Form_004_derivativeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Form_004_derivativeTable]

    GO

    CREATE TABLE [dbo].[Form_004_derivativeTable] (

     [ownershipDocument_ID] [int] NULL ,

     [securityTitle] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [conversionOrExercisePrice] [smallmoney] NULL ,

     [transactionDate] [smalldatetime] NULL ,

     [transactionFormType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [transactionCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [equitySwapInvolved] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [transactionShares] [int] NULL ,

     [transactionPricePerShare] [smallmoney] NULL ,

     [transactionAcquiredDisposedCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [exerciseDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [expirationDate] [smalldatetime] NULL ,

     [underlyingSecurityTitle] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [underlyingSecurity! Shares] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [sharesOwnedFollowingTransaction] [int] NULL ,

     [directOrIndirectOwnership] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    4)

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Form_004_footnotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Form_004_footnotes]

    GO

    CREATE TABLE [dbo].[Form_004_footnotes] (

     [ownershipDocument_ID] [int] NULL ,

     [footnotes1] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [footnotes2] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [footnotes3] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [footnotes4] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [footnotes5] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [footnotes6] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    ===================================================================================================
    ###########  SPROC   ##########################################################################
    CREATE PROC   _sp_Insert_Form_004_XML_ExistingTable1   @Form_004   nText
    AS
    DECLARE @iDoc int
    EXEC sp_xml_preparedocument @iDoc OUTPUT,  @Form_004
    BEGIN TRANSACTION
    --   1    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    INSERT Form_004_ownershipDocument

    (

     ownershipDocument,

     schemaVersion,

     documentType,

     periodOfReport ,

     notSubjectToSection16,

     issuerCik,

     issuerName,

     issuerTradingSymbol,

     rptOwnerCik,

     rptOwnerName,

     rptOwnerStreet1,

     rptOwnerStreet2,

     rptOwnerCity,

     rptOwnerState,

     rptOwnerZipCode,

     rptOwnerStateDescription,

     isDirector,

     isOfficer,

     isTenPercentOwner,

     isOther,

     officerTitle,

     otherText,

     signatureName,

     signatureDate,

     ImportID--contains the internally generated value for @mp:id. This is then used by @mp:parentid

    )

    --1

    SELECT

     ownershipDocument,

     schemaVersion,

     documentType,

     periodOfReport ,

     notSubjectToSection16,

     issuerCik,

     issuerName,

     issuerTradingSymbol,

     rptOwnerCik,

     rptOwnerName,

     rptOwnerStreet1,

     rptOwnerStreet2,

     rptOwnerCity,

     rptOwnerState,

     rptOwnerZipCode,

     rptOwnerStateDescription,

     isDirector,

     isOfficer,

     isTenPercentOwner,

     isOther,

     officerTitle,

     otherText,

     signatureName,

     signatureDate,

     ImportID--contains the internally generated value for @mp:id. This is then used by @mp:parentid

    FROM
    OPENXML(@iDoc, 'ownershipDocument', 3)
    WITH--1

    (

     ownershipDocument ntext '/',

     schemaVersion char(10) 'schemaVersion',

     documentType char(10) 'documentType',

     periodOfReport datetime 'periodOfReport',

     notSubjectToSection16 char(10) 'notSubjectToSection16',

     issuerCik int 'issuer/issuerCik',

     issuerName char(50) 'issuer/issuerName',

     issuerTradingSymbol char(10) 'issuer/issuerTradingSymbol',

     rptOwnerCik int 'reportingOwner/reportingOwnerId/rptOwnerCik',

     rptOwnerName char(50) 'reportingOwner/reportingOwnerId/rptOwnerName',

     rptOwnerStreet1 char(50) 'reportingOwner/reportingOwnerAddress/rptOwnerStreet1',

     rptOwnerStreet2 char(50) 'reportingOwner/reportingOwnerAddress/rptOwnerStreet2',

     rptOwnerCity char(50) 'reportingOwner/reportingOwnerAddress/rptOwnerCity',

     rptOwnerState char(10) 'reportingOwner/reportingOwnerAddress/rptOwnerState',

     rptOwnerZipCode int 'reportingOwner/reportingOwnerAddress/rptOwnerZipCode',

     rptOwnerStateDescription char(50) 'reportingOwner/reportingOwnerAddress/rptOwnerStateDescription',

     isDirector int 'reportingOwner/reportingOwnerRelationship/isDirector',

     isOfficer int 'reportingOwner/reportingOwnerRelationship/isOfficer',

     isTenPercentOwner int 'reportingOwner/reportingOwnerRelationship/isTenPercentOwner',

     isOther int 'reportingOwner/reportingOwnerRelationship/isOther',

     officerTitle char(20) 'reportingOwner/reportingOwnerRelationship/officerTitle',

     otherText char(100) 'reportingOwner/reportingOwnerRelationship/otherText',

     signatureName char(50) 'ownerSignature/signatureName',

     signatureDate datetime 'ownerSignature/signatureDate',

     Import_ID int '@mp:id'--contains the value generated by @mp:id. These values are then retrieved later by @mp:parentid

    )

    --    2    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    INSERT Form_004_nonDerivativeTable

    (

     ownershipDocument_ID,--include auto-increment column here

     securityTitle,

     conversionOrExercisePrice,

     transactionDate,

     transactionFormType,

     transactionCode,

     equitySwapInvolved,

     transactionShares,

     transactionPricePerShare,

     transactionAcquiredDisposedCode,

     exerciseDate,

     expirationDate,

     underlyingSecurityTitle,

     underlyingSecurityShares,

     sharesOwnedFollowingTransaction,

     directOrIndirectOwnership

    )

    --2

    SELECT

     Form_004_ownershipDocument.ownershipDocument_ID,--include auto-increment column here

     securityTitle,

     conversionOrExercisePrice,

     transactionDate,

     transactionFormType,

     transactionCode,

     equitySwapInvolved,

     transactionShares,

     transactionPricePerShare,

     transactionAcquiredDisposedCode,

     exerciseDate,

     expirationDate,

     underlyingSecurityTitle,

     underlyingSecurityShares,

     sharesOwnedFollowingTransaction,

     directOrIndirectOwnership

    FROM
    OPENXML(@iDoc, 'ownershipDocument/nonDerivativeTable/nonDerivativeTransaction', 3)
    WITH--2

    (

     ownershipDocument_ParentID  int '@mp:parentid',

     securityTitle char(100) 'securityTitle/value',

     conversionOrExercisePrice smallmoney 'conversionOrExercisePrice/value',

     transactionDate smalldatetime 'transactionDate/value',

     transactionFormType char(10) 'transactionCoding/transactionFormType/value',

     transactionCode char(10) 'transactionCoding/transactionCode/value',

     equitySwapInvolved char(10) 'transactionCoding/equitySwapInvolved/value',

     transactionShares int 'transactionAmounts/transactionShares/value',

     transactionPricePerShare smallmoney 'transactionAmounts/transactionPricePerShare/value',

     transactionAcquiredDisposedCode char(10) 'transactionAmounts/transactionAcquiredDisposedCode/value',

     exerciseDate char(10) 'exerciseDate/footnoteId[@id="F1"]',

     expirationDate smalldatetime 'expirationDate/value',

     underlyingSecurityTitle char(100) 'underlyingSecurity/underlyingSecurityTitle/value',

     underlyingSecurityShares char(10) 'underlyingSecurity/underlyingSecurityShares/value',

     sharesOwnedFollowingTransaction int 'postTransactionAmounts/sharesOwnedFollowingTransaction/value',

     directOrIndirectOwnership char(10) 'ownershipNature/directOrIndirectOwnership/value'

    )

    AS

    oxml2

    JOIN

    Form_004_ownershipDocument

    ON

    oxml2.ownershipDocument_ParentID = Form_004_ownershipDocument.ImportID

     
    --    3    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    INSERT Form_004_derivativeTable

    (

     ownershipDocument_ID,--include auto-increment column here

     securityTitle,

     conversionOrExercisePrice,

     transactionDate,

     transactionFormType,

     transactionCode,

     equitySwapInvolved,

     transactionShares,

     transactionPricePerShare,

     transactionAcquiredDisposedCode,

     exerciseDate,

     expirationDate,

     underlyingSecurityTitle,

     underlyingSecurityShares,

     sharesOwnedFollowingTransaction,

     directOrIndirectOwnership

    )

    --3

    SELECT

     Form_004_ownershipDocument.ownershipDocument_ID,

     securityTitle,

     conversionOrExercisePrice,

     transactionDate,

     transactionFormType,

     transactionCode,

     equitySwapInvolved,

     transactionShares,

     transactionPricePerShare,

     transactionAcquiredDisposedCode,

     exerciseDate,

     expirationDate,

     underlyingSecurityTitle,

     underlyingSecurityShares,

     sharesOwnedFollowingTransaction! ,

    &nbs p;directOrIndirectOwnership

    FROM
    OPENXML(@iDoc, 'ownershipDocument/derivativeTable/derivativeTransaction', 3)
    WITH

    (

     ownershipDocument_ParentID  int '@mp:parentid',

     securityTitle char(100) 'securityTitle/value',

     conversionOrExercisePrice smallmoney 'conversionOrExercisePrice/value',

     transactionDate smalldatetime 'transactionDate/value',

     transactionFormType char(10) 'transactionCoding/transactionFormType/value',

     transactionCode char(10) 'transactionCoding/transactionCode/value',

     equitySwapInvolved char(10) 'transactionCoding/equitySwapInvolved/value',

     transactionShares int 'transactionAmounts/transactionShares/value',

     transactionPricePerShare smallmoney 'transactionAmounts/transactionPricePerShare/value',

     transactionAcquiredDisposedCode char(10) 'transactionAmounts/transactionAcquiredDisposedCode/value',

     exerciseDate char(10) 'exerciseDate/footnoteId[@id="F1"]',

     expirationDate smalldatetime 'expirationDate/value',

     underlyingSecurityTitle char(100) 'underlyingSecurity/underlyingSecurityTitle/value',

     underlyingSecurityShares char(10) 'underlyingSecurity/underlyingSecurityShares/value',

     sharesOwnedFollowingTransaction int 'postTransactionAmounts/sharesOwnedFollowingTransaction/value',

     directOrIndirectOwnership char(10) 'ownershipNature/directOrIndirectOwnership/value'

    )

    AS

    oxml3

    JOIN

    Form_004_ownershipDocument

    ON

    oxml3.ownershipDocument_ParentID = Form_004_ownershipDocument.ImportID

     
    --    4    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    INSERT Form_004_footnotes

    (

     ownershipDocument_ID,--include auto-increment column here

     footnotes1,

     footnotes2,

     footnotes3,

     footnotes4,

     footnotes5,

     footnotes6

    )

    --4

    SELECT

     Form_004_ownershipDocument.ownershipDocument_ID,

     footnotes1,

     footnotes2,

     footnotes3,

     footnotes4,

     footnotes5,

     footnotes6

    FROM
    OPENXML(@iDoc, 'ownershipDocument/footnotes', 3)
    WITH--4

    (

     ownershipDocument_ParentID int '@mp:parentid',

     footnotes1 nvarchar(2000) 'footnote[@id="F1"]',

     footnotes2 nvarchar(2000) 'footnote[@id="F2"]',

     footnotes3 nvarchar(2000) 'footnote[@id="F3"]',

     footnotes4 nvarchar(2000) 'footnote[@id="F4"]',

     footnotes5 nvarchar(2000) 'footnote[@id="F5"]',

     footnotes6 nvarchar(2000) 'footnote[@id="F6"]'

    )

    AS

    oxml4

    JOIN

    Form_004_ownershipDocument

    ON

    oxml4.ownershipDocument_ParentID = Form_004_ownershipDocument.ImportID

               /*IF @@Error<>0

                  BEGIN

                    ROLLBACK TRANSACTION

                    RETURN(2)

               END*/

    COMMIT
    EXEC sp_xml_removedocument @iDoc

    GO

  • June 2, 2005 at 5:59 am

    #562471

    One error is that the line below is wrong....the 'Import_ID' should be 'ImportID' instead

     Import_ID int '@mp:id'--contains the value generated by @mp:id. These values are then retrieved later by @mp:parentid

    )

    --    2    ++++++++++++

    I still have another problem,  I made only that correction and ran the sproc...it inserts data into the Form_004_ownershipDocument and Form_004_footnotes tables ok, but doesn't seem to work as expected for the Form_004_derivativeTable and Form_004_nonDerivativeTable tables.

    There is a bit of a dearth of info about inserting hierarchical XML data into relational tables with IDENTITY columns...anyone happen to know what I'm trying to do here?

    The article that I'm trying to follow is this one by Rich Rollman:

    http://www.windowsitpro.com/SQLServer/Article/ArticleID/27473/27473.html   but I'm evidently not doing it quite right.

    Any further help would be appreciated greatly.

    Sincerely,

    Paul

  • June 7, 2005 at 12:00 am

    #563392

    Many Thanks  to Graeme Malcolm who kindly provided this solution that works.

    Paul

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

    Hi Paul,
    Richard forwarded your mail to me. I've taken a look and I'm a little confused about what you're trying to do. You seem to be using the mp:id from the parent to join to the child. I don't see how that's going to work. The IDs are only unique within each XML document instance, so if you do any subsequent inserts you'll end up with duplicate keys.
     
    One approach might be to generate a unique ID for the import as well as the parent ID for each parent record - here's a simple example:
     
    CREATE TABLE ParentTable

    (id int identity,

     parentData nvarchar(10),

     importID uniqueidentifier,

     importParentId int)

     
    CREATE TABLE ChildTable

    (parentId int,

     childData nvarchar(10)

    )

     
    -- Stored Proc code

    -- (doc would be passed in as parameter)

    DECLARE @doc nvarchar(2000)

    SET @doc = '<?xml version="1.0" ?>

                <XmlData>

                 <Parent data="parent1">

                  <Child data="child1a"/>

                  <Child data="child1b"/>

                 </Parent>

                 <Parent data="parent2">

                  <Child data="child2a"/>

                  <Child data="child2b"/>

                 </Parent>

                </XmlData>'

     
    DECLARE @importId uniqueidentifier

    SET @importId = newid()

     
    DECLARE @iDoc int

    EXEC sp_xml_preparedocument @iDoc OUTPUT, @doc

     
    INSERT INTO ParentTable (parentData, importId, importParentId)

    SELECT data, @importId, importParentId

    FROM OPENXML(@iDoc, 'XmlData/Parent', 1)

    WITH

    (data nvarchar(10),

     importParentId int '@mp:id')

     
    INSERT INTO ChildTable (parentID, childData)

    SELECT p.id, c.data

    FROM OPENXML(@iDoc, 'XmlData/Parent/Child', 1)

    WITH

    (data nvarchar(10),

     parentId int '@mp:parentid') AS c

    JOIN ParentTable AS p

    ON p.importParentId = c.parentId

    WHERE p.importId = @importId

     
    EXEC sp_xml_removedocument @iDoc
     

    The other approach would be to set the importParentId column to NULL at then end of the stored procedure so it doesn't cause problems on future inserts.
     
    On the other hand, you might want to look at the SQLXML Bulk Load component - that might be a better way to solve the problem.
     
    Hope that helps!
     
    Graeme

     
    --------------------------------------------------------------------
    Graeme Malcolm
    Principal Technologist
    Content Master - a member of CM Group Ltd.

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

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