Inserting to 4 Existing Tables
- 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 nTextASDECLARE @iDoc intEXEC sp_xml_preparedocument @iDoc OUTPUT, @Form_004BEGIN 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
FROMOPENXML(@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
FROMOPENXML(@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
FROMOPENXML(@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
FROMOPENXML(@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<>0BEGIN
ROLLBACK TRANSACTION
RETURN(2)
END*/
COMMITEXEC sp_xml_removedocument @iDocGO
-
This was removed by the editor as SPAM
-
Paul,
You may get more luck if you try and break down your problem.. that's an awful lot of code for someone to try and work out what's going on!
Alex
-
1. It helps if you have valid XML. I parsed yours into an XML file and found several errors. Searching for & should find several occurances of bad attempts to use which is not recognized in XML. That's an HTML string.
2. It helps if your select string uses the same variable name. Import_ID does not match ImportID.
I first tried creating just the select statement you were having trouble with in the proc. When executed it gave a totally misleading line number for the error message.
I put your XML into a file to see what the browser said. It barfed several times until I got rid of those several errors.
Then I executed the select statement through the proc and it gave an answer. Whether 0 for the id is what you'd expect when that's the only thing being executed is another matter. It gave an answer because I had automatically removed the _ to see if the select worked first. Once I put the _ back into Import_ID, it barfed again.
Like the other dude said, simplify your problems down to the trouble areas. Since I have no desire to actually create your tables and run your code, what other problems you may have with the code isn't known by me.
-
Many Thanks to Graeme Malcolm who kindly provided the answer below--that works.
To Alex and Kenneth...thanks for your suggestions...I'm having problems with the schema for the xml file (the guys at the SEC--where the document came from--assured me that the xml itself is valid.)
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 uniqueidentifierSET @importId = newid()
DECLARE @iDoc intEXEC 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 cJOIN ParentTable AS p
ON p.importParentId = c.parentId
WHERE p.importId = @importId
EXEC sp_xml_removedocument @iDocThe 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 MalcolmPrincipal TechnologistContent Master - a member of CM Group Ltd.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply