Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 OpenXML Inserting to 4 Existing Tables

Author  Topic 

kloepper
Yak Posting Veteran

77 Posts
Posted - 2005-06-01 : 10:19:36
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>
<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-Qualified 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>
</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

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,
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


===================================================================================================

kloepper
Yak Posting Veteran

77 Posts
Posted - 2005-06-02 : 07:49:14
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
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site