XML into a table

Question

XML into a table

Dan Guzman - Not the MVP

Hall of Fame

Points: 3757
More actions
November 20, 2009 at 3:43 pm

#206698

How do I do this?
I have a XML data type with XML text in it. How do I view the XML as a
table? I know it's un-normalized.
Here is the code I'm trying to use (from BOL):
DECLARE @docHandle int
DECLARE @xmlDocument XML-- nvarchar(max) -- or xml type
SELECT @xmlDocument = [xml_data]
FROM [subscriptions].[dbo].[XmlImportTest]
WHERE id=1
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT tblInsiders
SELECT *
FROM OPENXML(@docHandle, N'/')
This just gives me an edge table, but to define the WITH statement I
need to know what this xml looks like, which I don't. I tried using the
edge table information to build a table, but I have name duplication
problems all over the place because of the un-normalized structure of
the XML.
Is there some way to view this, just so I can look at the whole thing,
with repeated columns as next rows?
I've also looked at some of the XQuery options which gets me closer:
select [XML_Data].query('//issuerCik').value('.', 'integer') as CIK
,[XML_Data].query('//securityTitle').value('.', 'varchar(max)') as SecurityTitle
,[XML_Data].query('//issuerTradingSymbol').value('.', 'varchar(4)') as Ticker
from dbo.[XmlImportTest]
but if you actually run this against the XML I posted below, it returns all iterations of securityTitle in a single field in a single row. How do I get multiple rows for these multiple records within the element?
Here is the XML: - It's the stock market, but its essentially customers
and orders, and orders have notes.
<ownershipDocument>
<schemaVersion>X0303</schemaVersion>
<documentType>4</documentType>
<periodOfReport>2009-10-21</periodOfReport>
<notSubjectToSection16>0</notSubjectToSection16>
<issuer>
<issuerCik>0001000045</issuerCik>
<issuerName>NICHOLAS FINANCIAL INC</issuerName>
<issuerTradingSymbol>NICK</issuerTradingSymbol>
</issuer>
<reportingOwner>
<reportingOwnerId>
<rptOwnerCik>0001265079</rptOwnerCik>
<rptOwnerName>VOSOTAS PETER L</rptOwnerName>
</reportingOwnerId>
<reportingOwnerAddress>
<rptOwnerStreet1>2454 MCMULLEN BOOTH ROAD</rptOwnerStreet1>
<rptOwnerStreet2>BLDG C SUITE 501B</rptOwnerStreet2>
<rptOwnerCity>CLEARWATER</rptOwnerCity>
<rptOwnerState>FL</rptOwnerState>
<rptOwnerZipCode>33759</rptOwnerZipCode>
<rptOwnerStateDescription />
</reportingOwnerAddress>
<reportingOwnerRelationship>
<isDirector>1</isDirector>
<isOfficer>1</isOfficer>
<isTenPercentOwner>1</isTenPercentOwner>
<isOther>0</isOther>
<officerTitle>President & CEO</officerTitle>
</reportingOwnerRelationship>
</reportingOwner>
<nonDerivativeTable>
<nonDerivativeTransaction>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<transactionDate>
<value>2009-10-21</value>
</transactionDate>
<transactionCoding>
<transactionFormType>4</transactionFormType>
<transactionCode>M</transactionCode>
<equitySwapInvolved>0</equitySwapInvolved>
</transactionCoding>
<transactionTimeliness>
<value />
</transactionTimeliness>
<transactionAmounts>
<transactionShares>
<value>75000</value>
</transactionShares>
<transactionPricePerShare>
<value>1.5833</value>
</transactionPricePerShare>
<transactionAcquiredDisposedCode>
<value>A</value>
</transactionAcquiredDisposedCode>
</transactionAmounts>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>299251</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>D</value>
</directOrIndirectOwnership>
</ownershipNature>
</nonDerivativeTransaction>
<nonDerivativeHolding>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>36134</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>I</value>
</directOrIndirectOwnership>
<natureOfOwnership>
<value>Spouse</value>
</natureOfOwnership>
</ownershipNature>
</nonDerivativeHolding>
<nonDerivativeHolding>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>1200168</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>I</value>
</directOrIndirectOwnership>
<natureOfOwnership>
<value>Vosotas Family Trust</value>
</natureOfOwnership>
</ownershipNature>
</nonDerivativeHolding>
</nonDerivativeTable>
<derivativeTable>
<derivativeTransaction>
<securityTitle>
<value>Stock Option (right to buy)</value>
</securityTitle>
<conversionOrExercisePrice>
<value>1.5833</value>
</conversionOrExercisePrice>
<transactionDate>
<value>2009-10-21</value>
</transactionDate>
<transactionCoding>
<transactionFormType>4</transactionFormType>
<transactionCode>C</transactionCode>
<equitySwapInvolved>0</equitySwapInvolved>
</transactionCoding>
<transactionTimeliness>
<value />
</transactionTimeliness>
<transactionAmounts>
<transactionShares>
<value>75000</value>
</transactionShares>
<transactionPricePerShare>
<value>1.5833</value>
</transactionPricePerShare>
<transactionAcquiredDisposedCode>
<value>D</value>
</transactionAcquiredDisposedCode>
</transactionAmounts>
<exerciseDate>
<footnoteId id="F1" />
</exerciseDate>
<expirationDate>
<value>2009-11-08</value>
</expirationDate>
<underlyingSecurity>
<underlyingSecurityTitle>
<value>Common Stock</value>
</underlyingSecurityTitle>
<underlyingSecurityShares>
<value>75000</value>
</underlyingSecurityShares>
</underlyingSecurity>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>0</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>D</value>
</directOrIndirectOwnership>
</ownershipNature>
</derivativeTransaction>
<derivativeHolding>
<securityTitle>
<value>Stock Option (right to buy)</value>
</securityTitle>
<conversionOrExercisePrice>
<value>2.62</value>
</conversionOrExercisePrice>
<exerciseDate>
<footnoteId id="F2" />
</exerciseDate>
<expirationDate>
<value>2019-03-31</value>
</expirationDate>
<underlyingSecurity>
<underlyingSecurityTitle>
<value>Common STock</value>
</underlyingSecurityTitle>
<underlyingSecurityShares>
<value>50000</value>
</underlyingSecurityShares>
</underlyingSecurity>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>50000</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>D</value>
</directOrIndirectOwnership>
</ownershipNature>
</derivativeHolding>
<derivativeHolding>
<securityTitle>
<value>Stock Option (right to buy)</value>
</securityTitle>
<conversionOrExercisePrice>
<value>2.58</value>
</conversionOrExercisePrice>
<exerciseDate>
<footnoteId id="F3" />
</exerciseDate>
<expirationDate>
<value>2019-04-01</value>
</expirationDate>
<underlyingSecurity>
<underlyingSecurityTitle>
<value>Common Stock</value>
</underlyingSecurityTitle>
<underlyingSecurityShares>
<value>25000</value>
</underlyingSecurityShares>
</underlyingSecurity>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>25000</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
<ownershipNature>
<directOrIndirectOwnership>
<value>D</value>
</directOrIndirectOwnership>
</ownershipNature>
</derivativeHolding>
</derivativeTable>
<footnotes>
<footnote id="F1">The option vested in three equal annual
installments beginning November 8, 2000.</footnote>
<footnote id="F2">The option will vest in two equal annual
installments beginning March 31, 2009.</footnote>
<footnote id="F3">The option will vest in two equal annual
installments beginning April 1, 2009.</footnote>
</footnotes>
<ownerSignature>
<signatureName>Peter L Vosotas</signatureName>
<signatureDate>2009-10-27</signatureDate>
</ownerSignature>
</ownershipDocument>

Viewing 14 posts - 1 through 13 (of 13 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

One possible reason for not having an answer yet might be your rather large xml together with a missing result set to compare to.

Try to reduce your sample xml to the required amount and include your expected result.

I'm sure it won't take too long before you'll get an answer...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 2

Here is some shortened XML, I'll work on the example output next.

<issuerName>NICHOLAS FINANCIAL INC</issuerName>

</issuer>

<value>Common Stock</value>

</securityTitle>

</transactionDate>

</transactionShares>

</transactionPricePerShare>

</transactionAmounts>

</nonDerivativeTransaction>

<value>Common Stock</value>

</securityTitle>

</transactionDate>

</transactionShares>

</transactionPricePerShare>

</transactionAmounts>

</nonDerivativeTransaction>

<value>Common Stock</value>

</securityTitle>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

</nonDerivativeHolding>

<value>Common Stock</value>

</securityTitle>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

</nonDerivativeHolding>

</nonDerivativeTable>

<footnote id="F1">The option vested in three equal annual

installments beginning November 8, 2000.</footnote>

<footnote id="F2">The option will vest in two equal annual

installments beginning March 31, 2009.</footnote>

<footnote id="F3">The option will vest in two equal annual

installments beginning April 1, 2009.</footnote>

</footnotes>

</ownershipDocument>

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 3

Desired output from the above XML:

issuerCikissuerNameissuerTradingSymbolsecurityTitletransactionDatetransactionSharestransactionPricePerSharesharesOwnedFollowingTransactionfootnote IDfootnotes

1000045NICHOLAS FINANCIAL INCNICKCommon Stock10/21/2009750001.58330F1

1000045NICHOLAS FINANCIAL INCNICKCommon Stock10/22/2009361341.58331200168F2, F3

1000045NICHOLAS FINANCIAL INCNICKF1The option vested in three equal annual installments beginning November 8, 2000.</footnote>

1000046NICHOLAS FINANCIAL INCNICKF2The option will vest in two equal annual installments beginning March 31, 2009.</footnote>

1000047NICHOLAS FINANCIAL INCNICKF3The option will vest in two equal annual installments beginning April 1, 2009.</footnote>

Again, it's un-normalized, so I expect some redundant items.

Thanks for looking.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Unfortunately, your expected output does not match your sample data (e.g. there is no issuerCik with a value of 1000047).

Also, the table structure of your expected output is not clear at all. Please provide table definition and insert statement to ge a "real table" instead of an unsorted list. (I call it "unsorted" because it does not really make sense to have a column called [securityTitle] with a value of "F1", for instance - at least that's what your sample looks like...)

I strongly recommend to treat the data as they are presented: as separate groups (=separate table).

Get one list for the content of all [nonDerivativeTransaction] elements, one for [nonDerivativeHolding], try to figure out how those two actually match (there's nothing inside the xml that actually supports your assumption, that [sharesOwnedFollowingTransaction]=1200168 belongs to [transactionDate]='2009-10-22' - those are two completely unrelated nodes, as per xml definition).

Finally, get a separate table with your footnotes.

From that point on you're talking about relational data and it's fairly easy to join.

As an example here's a script to get the data for the first table:

SELECT c2.value('issuerCik[1]', 'varchar(30)') AS issuerCik,

c2.value('issuerName[1]', 'varchar(30)') AS issuerName,

c2.value('issuerTradingSymbol[1]', 'varchar(30)') AS issuerTradingSymbol,

c3.value('./securityTitle[1]/value[1]', 'varchar(30)') AS securityTitle,

c3.value('./transactionDate[1]/value[1]', 'varchar(30)') AS transactionDate,

c3.value('./transactionAmounts[1]/transactionShares[1]/value[1]', 'varchar(30)') AS transactionShares,

c3.value('./transactionAmounts[1]/transactionPricePerShare[1]/value[1]', 'varchar(30)') AS transactionPricePerShare,

c3.value('./footnoteId[1]/@id', 'varchar(30)') AS footnoteId

FROM @xml.nodes('ownershipDocument') T(c)

CROSS apply c.nodes ('issuer') T2(c2)

CROSS apply c.nodes ('nonDerivativeTable/nonDerivativeTransaction') T3(c3)

/* result set

issuerCikissuerNameissuerTradingSymbolsecurityTitletransactionDatetransactionSharestransactionPricePerSharefootnoteId

0001000045NICHOLAS FINANCIAL INCNICKCommon Stock2009-10-21750001.5833F1

0001000045NICHOLAS FINANCIAL INCNICKCommon Stock2009-10-22361341.5833F2,F3

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 5

SecurityTitle was not F1 in my Excel sheet I used to build that example, sorry.

You are also correct that the DerivitiveTables have no relation to NonDerivativetables.

I absolutely want separate tables, that is my goal, but I started with flat, because I know how to do the rest.

Your example script is really what I was after, it's the syntax I don't know, and with this example, i might be able to figure out how to build the three or four tables I need.

Thanks lmu92

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 6

Dan Guzman - Not the MVP

Hall of Fame

Points: 3757

November 24, 2009 at 5:49 pm

#1083756

Imu92, what is @xml?

LutzM SSC Guru Points: 107049 More actions · Answer 7

Dan Guzman - Not the MVP (11/24/2009)
Imu92, what is @xml?

I'm sorry... I used an XML variable to test the XQuery against.

Using the table name and yolumn you mentioned before it should be

SELECT c2.value('issuerCik[1]', 'varchar(30)') AS issuerCik,

c2.value('issuerName[1]', 'varchar(30)') AS issuerName,

c2.value('issuerTradingSymbol[1]', 'varchar(30)') AS issuerTradingSymbol,

c3.value('./securityTitle[1]/value[1]', 'varchar(30)') AS securityTitle,

c3.value('./transactionDate[1]/value[1]', 'varchar(30)') AS transactionDate,

c3.value('./transactionAmounts[1]/transactionShares[1]/value[1]', 'varchar(30)') AS transactionShares,

c3.value('./transactionAmounts[1]/transactionPricePerShare[1]/value[1]', 'varchar(30)') AS transactionPricePerShare,

c3.value('./footnoteId[1]/@id', 'varchar(30)') AS footnoteId

FROM dbo.[XmlImportTest]

CROSS apply [XML_Data].nodes('ownershipDocument') T(c)

CROSS apply c.nodes ('issuer') T2(c2)

CROSS apply c.nodes ('nonDerivativeTable/nonDerivativeTransaction') T3(c3)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 8

Thanks, that helped.

Why do I get 4 records instead of 2 when I run this:

SELECT c2.value('issuerCik[1]', 'varchar(30)') AS issuerCik,

c2.value('issuerName[1]', 'varchar(30)') AS issuerName,

c2.value('issuerTradingSymbol[1]', 'varchar(30)') AS issuerTradingSymbol,

c3.value('./securityTitle[1]/value[1]', 'varchar(30)') AS securityTitle,

c3.value('./transactionDate[1]/value[1]', 'varchar(30)') AS transactionDate,

c3.value('./transactionAmounts[1]/transactionShares[1]/value[1]', 'varchar(30)') AS transactionShares,

c3.value('./transactionAmounts[1]/transactionPricePerShare[1]/value[1]', 'varchar(30)') AS transactionPricePerShare,

c3.value('./footnoteId[1]/@id', 'varchar(30)') AS footnoteId

FROM dbo.tblInsiderXMLData

CROSS apply [XML_Data].nodes('ownershipDocument') T(c)

CROSS apply c.nodes ('issuer') T2(c2)

CROSS apply c.nodes ('nonDerivativeTable/nonDerivativeTransaction') T3(c3)

against this XML:

<issuerName>BLOUNT INTERNATIONAL INC</issuerName>

</issuer>

<rptOwnerName>BLOUNT INTERNATIONAL INC</rptOwnerName>

</reportingOwnerId>

<rptOwnerStreet1>4909 SE INTERNATIONAL WAY</rptOwnerStreet1>

<rptOwnerCity>PORTLAND</rptOwnerCity>

</reportingOwnerAddress>

<officerTitle>President, COO & CEO Designate</officerTitle>

</reportingOwnerRelationship>

</reportingOwner>

<value>Common Stock</value>

</securityTitle>

</transactionDate>

</transactionCoding>

</transactionShares>

</transactionPricePerShare>

</transactionAcquiredDisposedCode>

</transactionAmounts>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

</directOrIndirectOwnership>

</natureOfOwnership>

</ownershipNature>

</nonDerivativeTransaction>

<value>Common Stock</value>

</securityTitle>

</transactionDate>

</transactionCoding>

</transactionShares>

</transactionPricePerShare>

</transactionAcquiredDisposedCode>

</transactionAmounts>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

</directOrIndirectOwnership>

</natureOfOwnership>

</ownershipNature>

</nonDerivativeTransaction>

</nonDerivativeTable>

<signatureName>Richard H. Irving, III</signatureName>

</ownerSignature>

</ownershipDocument>

LutzM SSC Guru Points: 107049 More actions · Answer 9

What is the table structure of tblInsiderXMLData ?

And how many rows are in that table?

Since I cannot confirm your statement (your data will return only two rows when I run it against my temp table), I'd guess you have more than one row in your table.

SELECT dbo.tblInsiderXMLData.[another column],

c2.value('issuerCik[1]', 'varchar(30)') AS issuerCik,

... (rest as already shown)

and see if you can see where the second set is based on. Ideally, [another column] would be an identity column or another column uniquely identifying a row.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 10

Here is the table structure, but all the information you needed was in the XML I added to the end of the previous post.

CREATE TABLE [dbo].[tblInsiderXMLData](

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

[CIK] [int] NULL,

[xmlFileName] [varchar](300) NULL,

[xml_data] [xml] NULL,

[ImportDate] [datetime] NOT NULL CONSTRAINT [DF_tblInsiderXMLData_ImportDate] DEFAULT (getdate()),

[DateFiled] [varchar](50) NULL,

[SourceFolder] [varchar](255) NULL,

[FormType] [varchar](100) NULL,

LutzM SSC Guru Points: 107049 More actions · Answer 11

Can't really help.

here's the complete script I'm testing with (please note that I change the name of your original table):

CREATE TABLE [dbo].[tblInsiderXMLData_test](

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

[CIK] [int] NULL,

[xmlFileName] [varchar](300) NULL,

[xml_data] [xml] NULL,

[ImportDate] [datetime] NOT NULL CONSTRAINT [DF_tblInsiderXMLData_ImportDate] DEFAULT (getdate()),

[DateFiled] [varchar](50) NULL,

[SourceFolder] [varchar](255) NULL,

[FormType] [varchar](100) NULL)

INSERT INTO [tblInsiderXMLData_test]([xml_data]) SELECT

'<ownershipDocument>

<schemaVersion>X0303</schemaVersion>

<documentType>4</documentType>

<periodOfReport>2009-10-01</periodOfReport>

<issuer>

<issuerCik>0001001606</issuerCik>

<issuerName>BLOUNT INTERNATIONAL INC</issuerName>

<issuerTradingSymbol>BLT</issuerTradingSymbol>

</issuer>

<reportingOwner>

<reportingOwnerId>

<rptOwnerCik>0001001606</rptOwnerCik>

<rptOwnerName>BLOUNT INTERNATIONAL INC</rptOwnerName>

</reportingOwnerId>

<reportingOwnerAddress>

<rptOwnerStreet1>4909 SE INTERNATIONAL WAY</rptOwnerStreet1>

<rptOwnerStreet2 />

<rptOwnerCity>PORTLAND</rptOwnerCity>

<rptOwnerState>OR</rptOwnerState>

<rptOwnerZipCode>97222</rptOwnerZipCode>

<rptOwnerStateDescription />

</reportingOwnerAddress>

<reportingOwnerRelationship>

<isDirector>1</isDirector>

<isOfficer>1</isOfficer>

<isTenPercentOwner>0</isTenPercentOwner>

<isOther>0</isOther>

<officerTitle>President, COO CEO Designate</officerTitle>

</reportingOwnerRelationship>

</reportingOwner>

<nonDerivativeTable>

<nonDerivativeTransaction>

<securityTitle>

<value>Common Stock</value>

</securityTitle>

<transactionDate>

<value>2009-10-01</value>

</transactionDate>

<transactionCoding>

<transactionFormType>4</transactionFormType>

<transactionCode>P</transactionCode>

<equitySwapInvolved>0</equitySwapInvolved>

</transactionCoding>

<transactionAmounts>

<transactionShares>

<value>100</value>

</transactionShares>

<transactionPricePerShare>

<value>9.08</value>

</transactionPricePerShare>

<transactionAcquiredDisposedCode>

<value>A</value>

</transactionAcquiredDisposedCode>

</transactionAmounts>

<postTransactionAmounts>

<sharesOwnedFollowingTransaction>

<value>100</value>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

<ownershipNature>

<directOrIndirectOwnership>

<value>D</value>

</directOrIndirectOwnership>

<natureOfOwnership>

<value />

</natureOfOwnership>

</ownershipNature>

</nonDerivativeTransaction>

<nonDerivativeTransaction>

<securityTitle>

<value>Common Stock</value>

</securityTitle>

<transactionDate>

<value>2009-10-01</value>

</transactionDate>

<transactionCoding>

<transactionFormType>4</transactionFormType>

<transactionCode>P</transactionCode>

<equitySwapInvolved>0</equitySwapInvolved>

</transactionCoding>

<transactionAmounts>

<transactionShares>

<value>9900</value>

</transactionShares>

<transactionPricePerShare>

<value>9.10</value>

</transactionPricePerShare>

<transactionAcquiredDisposedCode>

<value>A</value>

</transactionAcquiredDisposedCode>

</transactionAmounts>

<postTransactionAmounts>

<sharesOwnedFollowingTransaction>

<value>10000</value>

</sharesOwnedFollowingTransaction>

</postTransactionAmounts>

<ownershipNature>

<directOrIndirectOwnership>

<value>D</value>

</directOrIndirectOwnership>

<natureOfOwnership>

<value />

</natureOfOwnership>

</ownershipNature>

</nonDerivativeTransaction>

</nonDerivativeTable>

<footnotes />

<remarks />

<ownerSignature>

<signatureName>Richard H. Irving, III</signatureName>

<signatureDate>2009-10-01</signatureDate>

</ownerSignature>

</ownershipDocument>'

SELECT [tblInsiderXMLData_test].id,

c2.value('issuerCik[1]', 'varchar(30)') AS issuerCik,

c2.value('issuerName[1]', 'varchar(30)') AS issuerName,

c2.value('issuerTradingSymbol[1]', 'varchar(30)') AS issuerTradingSymbol,

c3.value('./securityTitle[1]/value[1]', 'varchar(30)') AS securityTitle,

c3.value('./transactionDate[1]/value[1]', 'varchar(30)') AS transactionDate,

c3.value('./transactionAmounts[1]/transactionShares[1]/value[1]', 'varchar(30)') AS transactionShares,

c3.value('./transactionAmounts[1]/transactionPricePerShare[1]/value[1]', 'varchar(30)') AS transactionPricePerShare,

c3.value('./footnoteId[1]/@id', 'varchar(30)') AS footnoteId

FROM [tblInsiderXMLData_test]

CROSS apply [xml_data].nodes('ownershipDocument') T(c)

CROSS apply c.nodes ('issuer') T2(c2)

CROSS apply c.nodes ('nonDerivativeTable/nonDerivativeTransaction') T3(c3)

/*

idissuerCikissuerNameissuerTradingSymbolsecurityTitletransactionDatetransactionSharestransactionPricePerSharefootnoteId

10001001606BLOUNT INTERNATIONAL INCBLTCommon Stock2009-10-011009.08NULL

10001001606BLOUNT INTERNATIONAL INCBLTCommon Stock2009-10-0199009.10NULL

*/

As you can see: two rows only. Like I said: It seems like I don't have the complete data yet...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 12

My fault, sorry. Adding th eID into the output showed me that there were indeed 2 xml documents for that CIK.

I'll play with the nodes.

Thanks,

Dan

pham_p Valued Member Points: 58 More actions · Answer 13

Thank you for a wonderful article. It saves me a lot of time.