|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
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>
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 2,787,
Visits: 4,126
|
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
Here is some shortened XML, I'll work on the example output next.
<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>
<nonDerivativeTable>
<nonDerivativeTransaction>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<transactionDate>
<value>2009-10-21</value>
</transactionDate>
<transactionAmounts>
<transactionShares>
<value>75000</value>
</transactionShares>
<transactionPricePerShare>
<value>1.5833</value>
</transactionPricePerShare>
</transactionAmounts>
<footnoteId id="F1" />
</nonDerivativeTransaction>
<nonDerivativeTransaction>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<transactionDate>
<value>2009-10-22</value>
</transactionDate>
<transactionAmounts>
<transactionShares>
<value>36134</value>
</transactionShares>
<transactionPricePerShare>
<value>1.5833</value>
</transactionPricePerShare>
</transactionAmounts>
<footnoteId id="F2,F3" />
</nonDerivativeTransaction>
<nonDerivativeHolding>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>0</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
</nonDerivativeHolding>
<nonDerivativeHolding>
<securityTitle>
<value>Common Stock</value>
</securityTitle>
<postTransactionAmounts>
<sharesOwnedFollowingTransaction>
<value>1200168</value>
</sharesOwnedFollowingTransaction>
</postTransactionAmounts>
</nonDerivativeHolding>
</nonDerivativeTable>
<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>
</ownershipDocument>
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
Desired output from the above XML:
issuerCik issuerName issuerTradingSymbol securityTitle transactionDate transactionShares transactionPricePerShare sharesOwnedFollowingTransaction footnote ID footnotes
1000045 NICHOLAS FINANCIAL INC NICK Common Stock 10/21/2009 75000 1.5833 0 F1
1000045 NICHOLAS FINANCIAL INC NICK Common Stock 10/22/2009 36134 1.5833 1200168 F2, F3
1000045 NICHOLAS FINANCIAL INC NICK F1 The option vested in three equal annual installments beginning November 8, 2000.</footnote>
1000046 NICHOLAS FINANCIAL INC NICK F2 The option will vest in two equal annual installments beginning March 31, 2009.</footnote>
1000047 NICHOLAS FINANCIAL INC NICK F3 The 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.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 2,787,
Visits: 4,126
|
|
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
issuerCik issuerName issuerTradingSymbol securityTitle transactionDate transactionShares transactionPricePerShare footnoteId
0001000045 NICHOLAS FINANCIAL INC NICK Common Stock 2009-10-21 75000 1.5833 F1
0001000045 NICHOLAS FINANCIAL INC NICK Common Stock 2009-10-22 36134 1.5833 F2,F3
*/
Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
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
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 2,787,
Visits: 4,126
|
|
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 387,
Visits: 389
|
|
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:
<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>
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 2,787,
Visits: 4,126
|
|
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs
|
|
|
|
