Solved

Multipart XML Maniputlation and SQL Insert

Posted on 2004-08-30
5
334 Views
Last Modified: 2008-01-09
I have a unique situation.  I am going to be grabbing various XML documents from another website run by www.sec.gov.  I have already built the code to find the paths of each file I need to them import into my SQL database.  Here is the issue, the XML documents has a high level tags for <ownershipdocument>  then there are various that need to be put in for each record.  Then the document can have multiple tags with <DerivativeTable> and <NonDerivativeTable>.  Some of the subtags share the same tag names under the higher level tags.  

What I want to do is for each of these documents to create multiple records in my SQL Table with the individual names, addresses and document type then just one of the sections for the <derivativeTable> and <NonDerivativeTable>.  So the insert would be like this:

Person, address, document type, nonderivativeSection1
Person, address, document type, nonderivativeSection2
Person, address, document type, DerivativeSection1
Person, address, document type, DerivativeSection2
Person, address, document type, DerivativeSection3

I am integrating this into an ASP page using VBScript so that once I have provided the links that need to be imported, it would read through them all and when finished, the original set of links in the tempDB would be removed.  Here is a sample of the XML file that I will be pulling from ftp://ftp.sec.gov/edgar/data/99302/0000891839-04-000128.txt .  If you notice in the <nonDerivativeTable> there is only one section of <nonDerivativeHolding>.  In the <derivativeTable>  there are 3 sections of <derivativeHolding>.  What I would need is for the NonDerivativeHolding and the 3 DereivativeHolding sections to create a seperate record in the SQL table with all of the data above the NON seciton repeated.  

Please help.
0
Comment
Question by:pkaleda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:dualsoul
ID: 11935464
hm...it's not clear what technology you are using to work with XML?

For me, i would write XSL style to transform all kinds of your documents in some intermidiate XML format with ready-to-load data, and then use RDBM tools (if any) or write my own simple tools to insert data from this intermidiate XML to DB.
0
 
LVL 6

Author Comment

by:pkaleda
ID: 11937221
I am using XHLHTTP in ASP to grab the data from the site.  The pages are not XML only that I am pulling.  They have standard text headers and footers with no Markup so XSL always breaks.
0
 
LVL 15

Accepted Solution

by:
dualsoul earned 500 total points
ID: 11945651
hm...and the problem is?
you can cut out this headers and footers, does it problem? the rest will be XML?
0
 
LVL 6

Author Comment

by:pkaleda
ID: 11990582
Daulsoul,

DUHHHHHHHH.  Sometimes you are to close to see the simple fix.  Thanks.

0
 
LVL 15

Expert Comment

by:dualsoul
ID: 11991782
you are welcome :)
0

Featured Post

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question