XML Parsing

[Raftsman]

I have been rading the manual and forum t see if I cold somehow figure out how to get the following into a readable format. I have run into many problem. Below is a snipit of what the input is;

<!--BEGIN-->
<?xml version="1.0" encoding="UTF-8"?>
<CQMetricsRecord
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="CQMetrics.xsd">
<QueryRequest queryRequestId="144569" requestType="SINGLE"
systemId="IBQ" timeStamp="2007-02-28T13:52:03.154"
userId="JXZ122 " worklocationID="1300" worklocationSourceCode="1">
<Databases>
<Database dataSource="IMS" entityType="Person"
queryResultId="173953" subjectArea="IMS"/>
<Database dataSource="ICES" entityType="Person"
queryResultId="173954" subjectArea="CUS"/>
</Databases>
</QueryRequest>
</CQMetricsRecord>

<!--END-->

First I am having problems loading in the XML, I get a problem with line 3

<?xml version="1.0" encoding="UTF-8"?>

I tried to encode using UTF-8 and still couldn't get it to work so I stripped the row and the BEGIN and END and was able to import the structure.

After reading many posts, I tried to select a sequential=>XML input=> PEEK and got totally lost. I am not sure if the seq must be delimited in a way to read the XML file. I thought that XML Input would allow you to specify the XML file and take care of the rest.

Could someone please point me in the right direction.

Thanks.

[eostic]

Hi Jim...

For testing try Sequential to XMLInput to Sequential or Peek. In the sequential file, read a dummy file that has the exact name and path of your xml document as a VALUE in a single column called "documentName".

pass this into the XMLInput Stage and identify column documentName in the pull down and "URL" in the radio button....

on your output, start simple and have one column:

dataSource char(20) key=yes, Description= /CQMetricsRecord/QueryRequest/Databases/Database/@dataSource

Should work.

Ernie

[Raftsman]

I got the record to come in but I get 0 records out. Is there another setting?

Thanks

[eostic]

check the exact spellings and position of the element names and attribute name in the /xxx/yyy string above. If it's off by one little bit, you'll get zero records. That string is the "XPath" that leads to your attribute.

Ernie