The sproc below does NOT raise an error message in Query Analyzer,
but it DOES raise an error in the 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>
<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>
</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>
<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>
<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>
</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 ,
[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
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
EXECsp_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,
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
================================================== =
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/SQLServe...473/27473.html
but I'm evidently not doing it quite right.
Any further help would be appreciated greatly.
Sincerely,
Paul
|||First some somewhat unrelated questions and comments:
What do you expect the following to do:
ownershipDocument ntext '/',
You use flag 3. I normally recommend to use either 1 or 2 and use explicit
paths instead of using 3. Using 3 can be extremely slow on pre-SP4 SQL
Server 2000 installations.
Now to the problem:
You are using @.mp:id and @.mp:parentid to perform the identity value fix up
for the ids which is good. But you are using the wrong parent ids.
E.g., you use
OPENXML(@.iDoc, 'ownershipDocument', 3) WITH ... ImportID int '@.mp:id'
to get the id to related to the IDENTITY value.
which works with the following parent id:
OPENXML(@.iDoc, 'ownershipDocument/footnotes', 3) WITH ...
ownershipDocument_ParentID int '@.mp:parentid',
since ownershipDocuments are parents of footnotes.
But the following
OPENXML(@.iDoc,
'ownershipDocument/nonDerivativeTable/nonDerivativeTransaction', 3) WITH...
ownershipDocument_ParentID int '@.mp:parentid',
and
OPENXML(@.iDoc, 'ownershipDocument/derivativeTable/derivativeTransaction', 3)
WITH...ownershipDocument_ParentID int '@.mp:parentid',
does not work since @.mp:parentid gets the id of the nonDerivativeTable and
derivativeTable elements and not the ownershipDocument.
Suggested solutions are
1. to add one or two additional OpenXML extractions (into a temp table if
you do one and reuse it) to provide the missing join link.
2. to use the parent axis (works much better in SP4 than in previous 2000
versions) to get some actual identifying data instead of using
@.mp:parentid/@.mp:id. That would save you the additional OpenXML calls.
Best regards
Michael
"a" <a@.discussions.microsoft.com> wrote in message
news:BF304360-E478-4A06-B78D-DF438C0A621A@.microsoft.com...
> 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/SQLServe...473/27473.html
> but I'm evidently not doing it quite right.
> Any further help would be appreciated greatly.
> Sincerely,
> Paul
>
|||Michael:
Thanks for the reply, though I'm going to have to work on it a bit to
understand how to apply it to my problem.
I'm using this solution, provided by Graeme Malcolm...as you said, this adds
a few more openxml extractions (7 total)
Thanks, 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.
www.contentmaster.com
"Michael Rys [MSFT]" wrote:
> First some somewhat unrelated questions and comments:
> What do you expect the following to do:
> ownershipDocument ntext '/',
> You use flag 3. I normally recommend to use either 1 or 2 and use explicit
> paths instead of using 3. Using 3 can be extremely slow on pre-SP4 SQL
> Server 2000 installations.
> Now to the problem:
> You are using @.mp:id and @.mp:parentid to perform the identity value fix up
> for the ids which is good. But you are using the wrong parent ids.
> E.g., you use
> OPENXML(@.iDoc, 'ownershipDocument', 3) WITH ... ImportID int '@.mp:id'
> to get the id to related to the IDENTITY value.
> which works with the following parent id:
> OPENXML(@.iDoc, 'ownershipDocument/footnotes', 3) WITH ...
> ownershipDocument_ParentID int '@.mp:parentid',
> since ownershipDocuments are parents of footnotes.
>
> But the following
> OPENXML(@.iDoc,
> 'ownershipDocument/nonDerivativeTable/nonDerivativeTransaction', 3) WITH...
> ownershipDocument_ParentID int '@.mp:parentid',
> and
> OPENXML(@.iDoc, 'ownershipDocument/derivativeTable/derivativeTransaction', 3)
> WITH...ownershipDocument_ParentID int '@.mp:parentid',
> does not work since @.mp:parentid gets the id of the nonDerivativeTable and
> derivativeTable elements and not the ownershipDocument.
> Suggested solutions are
> 1. to add one or two additional OpenXML extractions (into a temp table if
> you do one and reuse it) to provide the missing join link.
> 2. to use the parent axis (works much better in SP4 than in previous 2000
> versions) to get some actual identifying data instead of using
> @.mp:parentid/@.mp:id. That would save you the additional OpenXML calls.
> Best regards
> Michael
> "a" <a@.discussions.microsoft.com> wrote in message
> news:BF304360-E478-4A06-B78D-DF438C0A621A@.microsoft.com...
>
>
Celestine:
about what you're trying to do. You seem to be using the mp:id from