Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
4
Erster Beitrag:
vor 7 Jahren, 9 Monaten
Letzter Beitrag:
vor 7 Jahren, 9 Monaten
Beteiligte Autoren:
steve erts, steve, King

solution... how to break up a multi record XML Document into a database table with one column for each XML Record

Startbeitrag von steve erts am 09.09.2010 01:40

I had an business issue where a client was giving me a large multi record XML file that I needed to break into individual records so that they can be transmitted one by one to a web service. To accomplish what I wanted I wanted the contents of each XML Record to be stored in a single database column.

I struggled with it quite a lot but finally hit on a solution with some tips from some generous people here so I wanted to share my solution in the hopes that it might help someone else in the future.


HourGlass(True)

XMLDoc is string="XMLDoc"
XMLInfo is string
svCurrentElement is string // The Current XML Element
svCurrentOpenTag is string // current tag
svCurrentCloseTag is string // current closing tag
svCurrentRecord is string // string to hold xml tags as they are being read and parsed into a single data record


XMLClose(XMLDoc) //Frees the XML document
XMLInfo=fLoadText("c:\cd_list.xml") // Loads the XML File into a string
XMLDocument(XMLDoc,XMLInfo) // converts the string into an XML Document that I can work with

XMLRoot(XMLDoc)
XMLFind(XMLDoc,Null) // starts reading the XML Document


WHILE NOT XMLOut(XMLDoc) // loops through XML Document

svCurrentCloseTag="" // clears last tag so it is not written at front of second data record
svcurrentopentag ="" // declares current opening tag

IF XMLElementName(XMLDoc)"CD" THEN // skips closing record tag
svCurrentCloseTag ="" // declares current closing tag
END
svCurrentElement=XMLData(XMLDoc) //declares current tag data


// build string that concatenates each tag & data into single record, don't close record tag (in this case ) until the last tag in record is read.

IF XMLElementName(XMLDoc) "YEAR" THEN
svCurrentRecord=svCurrentRecord+svCurrentOpenTag+svCurrentElement+svCurrentCloseTag

ELSE
svCurrentRecord=svCurrentRecord+svCurrentOpenTag+svCurrentElement+svCurrentCloseTag+""
END


// now add record to database table when last tag is read


IF XMLElementName(XMLDoc) = "YEAR" THEN
XML_Parse.XML_Record=svCurrentRecord
HAdd(XML_Parse)
svCurrentRecord=""
END



XMLNext(XMLDoc)
END
XMLCancelSearch(XMLDoc)

// display data in table in screen
TableDisplay(TABLE_XML_Parse)

HourGlass(False)

Antworten:

BTW... for those interested. The above code breaks up each individual record {CD} in the document below into a single column in a mysql database table. This is a proof of concept for a larger project in which I'll be taking tour booking records extracted in XML format from a legacy reservation system and transmitting them to my clients corporate accounting system.


CATALOG}
{CD}
{TITLE}Empire Burlesque{/TITLE}
{ARTIST}Bob Dylan{/ARTIST}
{COUNTRY}USA{/COUNTRY}
{COMPANY}Columbia{/COMPANY}
{PRICE}10.90{/PRICE}
{YEAR}1985{/YEAR}
{/CD}
{CD}
{TITLE}Hide your heart{/TITLE}
{ARTIST}Bonnie Tyler{/ARTIST}
{COUNTRY}UK{/COUNTRY}
{COMPANY}CBS Records{/COMPANY}
{PRICE}9.90{/PRICE}
{YEAR}1988{/YEAR}
{/CD}
{CD}
{TITLE}Greatest Hits{/TITLE}
{ARTIST}Dolly Parton{/ARTIST}
{COUNTRY}USA{/COUNTRY}
{COMPANY}RCA{/COMPANY}
{PRICE}9.90{/PRICE}
{YEAR}1982{/YEAR}
{/CD}

{/CATALOG}


von steve erts - am 09.09.2010 15:33
Steve

You can make life easier as below"

//
XMLSource is string = fLoadText("c:\cd_list.xml")

sTag is string = "CD"
sStartTag is string = ""
sEndTag is string = ""
sUnTagResult is string
sTagResult is string
i is int = 1

LOOP
sUnTagResult = XMLExtractString(XMLSource, sTag, i)
If sUnTagResult = "" break

// Extract the XML code found in the 1st "" tag and next in the loop
sTagResult = sStartTag + sUnTagResult + sEndTag
Trace(sTagResult)
//HAdd(XML_Parse)

i = i + 1

END //LOOP

//
Enjoy...
King


von King - am 10.09.2010 13:10
Thanks!

I like it.

von steve - am 11.09.2010 00:16
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.