0 Replies Latest reply: Jan 26, 2010 5:48 PM by Alex Mumme RSS

    XML export, transform and import

      Greetings All!

      I'm generally not the type to quickly ask for support.. but I've realized that by not asking for help, I'm depriving everyone else in the community of the chance to see problems that might crop up.. as well as their solutions!

      So, with that in mind, here we go:

      I'm working with a data system that spans multiple MySql databases. The schema of each individual data system contains reference to two databases on separate servers. All told, the load script has to load data from 3 different data systems for a total of 6 databases.

      That problem I solved, but this one I'm having issue with.

      As part of the data schema there is one field named XML that contains an entire XML document. Inside of this document are effectively 3 tables, which must then be linked to the loaded database schema. The XML that is in the field is different per record.

      How do I read the XML from this field with a load statement such that it becomes these three tables?

      I tried a few things, but ultimately didn't get even intermediary results, likely due to my not fully understanding the XML handling capabilities in QlikView. Below outlines the solution I implemented:

       

      1. Define a DSN-less connection string as a dollar sign expansion to enable easy connection to the multiple data sources
      2. Define dollar sign expansions to handle the common select/load statements across the systems
      3. Using the expansions, load the data from all the system into localized tables
      4. Call a JScript macro to export and transform the XML field as a single, external XML document
      5. Use the Table Files XML import wizard to load the XML
      6. Store all loaded tables as QVD's for Presentation layer use

       

      Below are the scripts as composed:

      [CODE]

      // System variable initializationSET ThousandSep=',';SET DecimalSep='.';SET MoneyThousandSep=',';SET MoneyDecimalSep='.';SET MoneyFormat='$#,##0.00;($#,##0.00)';SET TimeFormat='h:mm:ss TT';SET DateFormat='M/D/YYYY';SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';/** * Syntax transformation helpers: * Load script doesn't allow us to easily combine multiple statements * that require semicolon termination, i.e.: a LOAD statement followed * by it's matching SELECT statement. * The expansions $(b) and $(e) allow us to retain syntax highlighting, * and the $(compose) expansion enables us to chain multiple statements * together as a single expansion. * TODO: Reimplement $(b) and $(e) to use Replace() for syntax * transformation instead of apostrophes */LET b = Chr(39);LET e = Chr(39);LET compose = ' & ' & Chr(39) & '|' & Chr(39) & ' & '; // & '|' &SET compose = $(compose); // Not sure why, but this LET expansion seems to need to // be SET before it can be used./** * Usage: $(MySQL351Conn(servername,database,username,Xpassword) * The variable below must be contained on a single line, as QlikView can be very * white-space sensitive. See comment below for structure. */SET MySQL351Conn = CONNECT TO [ Provider=MSDASQL.1 ; Persist Security Info=True; User ID=$3; Mode=Read; Extended Properties=" DATABASE=$2; DRIVER={MySQL ODBC 3.51 Driver}; OPTION=3; PORT=3306; SERVER=$1; UID=$3;"; Initial Catalog=$2] (XPassword is $4);//SET MySQL351Conn = // CONNECT TO [ Provider=MSDASQL.1 ; Persist Security Info=True// ; User ID=$3 ; Mode=Read// ; Extended Properties=// " DATABASE=$2 ; DRIVER={MySQL ODBC 3.51 Driver}// ; OPTION=3 ; PORT=3306// ; SERVER=$1 ; UID=$3;"// ; Initial Catalog=$2] // (XPassword is $4);SET LoadEvalTable = $(b)LOAD id, assignmentKey, surveyType, costCenterNumber, assignmentStartDate, assignmentEndDate, lastSentDate, clientName, clientKey, worksiteName, worksiteKey, clientContactName, clientContactKey, providerName, providerKey, attemptNumber, sendMethod, status, surveyKey AS surveyId, specialty, team, createdDate, completedDate, worksiteState, webDescription, clientJde, worksiteJde, providerJde, clientRep, providerRep, companyNo $(e) $(compose) $(b)SQL SELECT * FROM qaevals.eval$(e);LET LoadEvalTable = Replace($(LoadEvalTable),'|', Chr(59)); // Transforms the composed statements into a single statementSET LoadEngineTables = $(b)LOAD id AS qId, questionId, questionKey, valueString, valueText, sequence, surveyId$(e)$(compose) $(b)SQL SELECT * FROM surveyEngine.question$(e)$(compose) $(b)LOAD id AS surveyId, application, descriptor, created, xml$(e)$(compose) $(b)SQL SELECT * FROM surveyEngine.survey$(e);LET LoadEngineTables = Replace($(LoadEngineTables),'|', Chr(59));$(MySQL351Conn(qaeval.company-1.com,qaevals,root,QNOETZOWbD));$(LoadEvalTable);$(MySQL351Conn(surveng.company-1.com,surveyEngine,root,QNOETZOWbD));$(LoadEngineTables);$(MySQL351Conn(qaeval.company-2.com,qaevals,root,QNOETZOWbD));$(LoadEvalTable);$(MySQL351Conn(surveng.company-2.com,surveyEngine,root,QNOETZOWbD));$(LoadEngineTables);$(MySQL351Conn(qaeval.company-3.com,qaevals,root,QNOETZOWbD));$(LoadEvalTable);$(MySQL351Conn(surveng.company-3.com,surveyEngine,root,QNOETZOWbD));$(LoadEngineTables);// Create questions.xmlLET nadda = exportXML();// Start of [questions.xml] LOAD statements[question/question]:LOAD text, key, required, %Key_question_0D553DC22775B199 // Key to parent table: survey/questionFROM [questions.xml] (XmlSimple, Table is [survey/question/question]);[survey/question]:LOAD text, key, required, [answer/type], [answer/readonly], [answer/height] as height, %Key_survey_713B0C62B2CAE35C, // Key to parent table: survey %Key_question_0D553DC22775B199 // Key for this table: survey/questionFROM [questions.xml] (XmlSimple, Table is [survey/question]);survey1:LOAD xslVersion, showSequence, title, description, bold, [data/text], %Key_survey_713B0C62B2CAE35C // Key for this table: surveyFROM [questions.xml] (XmlSimple, Table is [survey]);// End of [questions.xml] LOAD statementsquestionData:LOAD text As questionText , key As questionKeyRESIDENT [survey/question];LOAD text As questionText , key As questionKeyRESIDENT [question/question];DROP TABLE survey1, [survey/question], [question/question]; STORE question INTO QVDs\QAEA_question.qvd;STORE eval INTO QVDs\QAEA_eval.qvd;STORE survey INTO QVDs\QAEA_survey.qvd;STORE questionData INTO QVDs\QAEA_questionData.qvd;

      [/CODE]

      And below is the JScript for exportXML():

      [CODE]

      function testXML () { var fn= ActiveDocument.GetProperties().MyWorkingDirectory + "\\questions.xml" ; ActiveDocument.GetField("xml") . ServerSideExportEx(fn,".",1,0,false) ; var fo=(new ActiveXObject("Scripting.FileSystemObject")) . GetFile (fn) ; rf=fo . OpenAsTextStream(1,0) ; nt=rf . ReadAll() . replace(/""""/g,""" """) // Change correct "" to " " . replace(/""/g,""") . replace(/" "/g, """") // Change all "" to " then correct " " to "" . replace(/"\<\?/g,"<?") . replace(/ "[\n\r]+/g, "") // Remove record quotations ; rf . Close() ; wf= fo . OpenAsTextStream(2,0) ; wf . Write (nt) ; wf . Close() }

      [/CODE]
      The problem that's keeping this solution from working is that for some reason, attempting to call exportXML() from the Load Script hangs the Qlikview engine and requires a process kill in order to stop it.

      However, if I call exportXML() directly from within the Edit Module dialog, it works flawlessly.
      Appreciate your help and opinions!
      Alex