Unable to read XML data due to xmlns line

satkolli · Post by **satkolli** » Mon May 05, 2014 11:07 am

I have an XML with the following:
<customers xmlns="http://www.cust.org/Customers">
<customer customer-no="1234" mode="merge">
<credentials>
<login>none@email.com</login>
</credentials>
<profile>
<first-name>John</first-name>
<last-name>Smith</last-name>
<phone-home>100-555-1234</phone-home>
</profile>
</customer>
</customers>

For some reason I am able to read the contents from an XML Input stage into a flat file only when the xmlns text (in RED above) is NOT in the XML. When it is there the XML Input stage returns no rows. Can someone tell me what I am missing or need to do in the XML Input stage to be able to read the contents when the xmlns line is defined?

I tried adding the line xmlns="http://www.cust.org/Customers" to the Stage tab, Transformation settings' "Include namespace declaration" area and it does not help. Still getting no rows out. If I remove that line from the source XML the job works fine and I get the customer number and name into the flat file.

Help!

eostic · Post by **eostic** » Mon May 05, 2014 2:33 pm

One possibility is that it is being interpreted as a default namespace.

Take a look at the Description property in the resulting TableDef that was created when you imported this xml document via the xml metadata importer. Do any of the xpaths have ns1: or similar?

Did you load the namespace into the namespace box within the Link? (it's a LOAD button just like you see on the columns tabs of any stage).

Everything has to match, but sometimes it is tricky, and I simply have a transformer upstream that does exactly as you have stated --- I "zap" the namespace out of it dynamically.

Ernie

satkolli · Post by **satkolli** » Mon May 05, 2014 3:10 pm

Thanks for your response eostic.

I did not import the metadata and manually typed up the details into the Ouput tab's "Columns" of the XML Input stage. I added the XPaths manually in the Description as below:

Column DataType Description
CustomerNum Integer /customers/customer/@customer-no
login Varchar /customers/customer/credentials/login
fname Varchar /customers/customer/profile/first-name
lname Varchar /customers/customer/profile/last-name

The CustomerNum column is set as a Key column and the "Repetition element required" is set int he Transformation Settings.

eostic · Post by **eostic** » Mon May 05, 2014 4:09 pm

Ok. Great! Good to see others who know the stage well enough to hand code the xpath! Congrats.

In the meantime, import it now anyway and see if it puts a prefix on it, and you'll get an idea of how to edit your hand-coded xpath.

Ernie

satkolli · Post by **satkolli** » Tue May 06, 2014 8:50 am

I imported the table definitions for the XML and here is what it put:
Column,DataType,Description
customers,Varchar,/ns1:customers
customer_no,Decimal,/ns1:customers/ns1:customer/@customer-no
login,Varchar,/ns1:customers/ns1:customer/ns1:credentials/ns1:login/text()
first_name,Varchar,/ns1:customers/ns1:customer/ns1:profile/ns1:first-name/text()
last_name,Varchar,/ns1:customers/ns1:customer/ns1:profile/ns1:last-name/text()

I ran the job and it aborted with the error:
The prefix 'ns1' is not declared. expression = '/ns1:customers' Remaining tokens are: ('/')

Do I have to put something in the 'Include namespace declaration' of the Transformation Settings? Right now I have not declared anything there.

eostic · Post by **eostic** » Tue May 06, 2014 9:08 am

Yes. "import" that same table definition into the namespace area (as I noted in the first response). There is a LOAD button there. It will bring in the declaration.

Ernie

satkolli · Post by **satkolli** » Tue May 06, 2014 9:14 am

Yup... I was typing the same thing of using the Load button when I saw your response. That did it! Thanks.

In the "Include namespace declaration", I clicked on the Load button and loaded the info from the imported XML table definitions. That added the following lines:
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ns1="http://www.cust.org/Customers"

Now when I run the job with the "ns1" in the XPaths it ran fine and returned the data into my output flat file. So loading the namespace declarations fixed the issue. Thanks for the idea of importing the table definitions.

In the past I have creeated XMLs using the Output XML stage and did not have to deal with namespaces. The few times I used the Input stage I did not need the namespaces and manually typing the XPath has always worked. Will use the import feature more frequently now. The DS documentation does not have much info about the XML stages and how to use them with the namespaces. Or maybe I need to read up on XMLs in general. Where would be a good place to start?

eostic · Post by **eostic** » Tue May 06, 2014 3:14 pm

Namespaces can be a real pain. They have lofty goals in keeping things unique among multiple designers and other such objectives --- they are probably a complexity that is helping bring xml down. XML isn't going away anytime soon, but it's interesting to see how much JSON is filling in for xml where xml was the norm.....

Where to start? There are literally thousands of web sites out there that have tutorials on xml...

Start with "the" formal site --- www.w3.org and www.w3schools.com has always had very good content...I'd start with those two and then just start searching......

Ernie