Can't read field with XML Input stage

[thompsonp]

I have a source system that maintains audit information in a sql server database. One of the tables contains an XML field that I need to read.

My job goes from ODBC -> XML Input -> Sequential File.

As I have not used XML before I am just trying to read the XML field and convert it to a flat structure. When this works I will build the rest of the required functionality.

I have imported the XML Table Definition using the Import tool.

The XML file is shown below:

Code: Select all

<BusinessEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/crm/2006/WebServices" xsi:type="DynamicEntity" Name="account">
  <Properties>
    <Property xsi:type="CrmBooleanProperty" Name="brit_lloydsbroker">
      <Value>0</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_coverholder">
      <Value>0</Value>
    </Property>
    <Property xsi:type="PicklistProperty" Name="ownershipcode">
      <Value>7</Value>
    </Property>
    <Property xsi:type="PicklistProperty" Name="businesstypecode">
      <Value>1</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_ukd">
      <Value>0</Value>
    </Property>
    <Property xsi:type="StringProperty" Name="address1_country">
      <Value>United Kingdom</Value>
    </Property>
    <Property xsi:type="PicklistProperty" Name="brit_brokerapprovalstatus">
      <Value>13</Value>
    </Property>
    <Property xsi:type="StringProperty" Name="address1_line1">
      <Value>2 Watling Drive</Value>
    </Property>
    <Property xsi:type="StringProperty" Name="name">
      <Value>Ciber Europe Ltd</Value>
    </Property>
    <Property xsi:type="StringProperty" Name="brit_companylegalname">
      <Value>Ciber Europe Ltd</Value>
    </Property>
    <Property xsi:type="LookupProperty" Name="brit_uniquenumberid">
      <Value name="100000002" dsc="0">{B001442D-4F1E-DC11-BBE3-0003FFD062C1}</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_ownultimateparent">
      <Value>1</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_londonmarket">
      <Value>0</Value>
    </Property>
    <Property xsi:type="PicklistProperty" Name="brit_regionaloffice">
      <Value>4</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_broker">
      <Value>0</Value>
    </Property>
    <Property xsi:type="CrmBooleanProperty" Name="brit_ri">
      <Value>0</Value>
    </Property>
    <Property xsi:type="StringProperty" Name="address1_city">
      <Value>HINCKLEY</Value>
    </Property>
    <Property xsi:type="PicklistProperty" Name="brit_coverholderapprovalstatus">
      <Value>13</Value>
    </Property>
  </Properties>
</BusinessEntity>

I have included the namespace declaration by loading it from the table definition as:
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:ns1="http://schemas.microsoft.com/crm/2006/WebServices"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

When the job runs I get the following error and no rows are processed:

BrokerCRMAuditXML_fromDB..XML_Input_5: The name '{0}' is not a valid NCName.
expression = '/:BusinessEntity' Remaining tokens are: ('/')

Can anyone advise on what the problem might be please?

[rameshrr3]

Before i begin, im not familiar with DS 8.x. However some hints i can offer :

What are the Xpath expressions that you have defined in the output of XML input stage?

They should start with

Code: Select all

/BusinessEntity/Properties/Property

Eg.
If you want to access the value where xsi:type="CrmBooleanProperty" Name="brit_ukd"

The you Xpath for the field that you output the above data should be

Code: Select all

/BusinessEntity/Properties/Property[@xsi:type="CrmBooleanProperty" and @Name="brit_ukd"]/text()

Of course Im not sure if the the XML metadata importer that ships with DS 8.x gives you the attribute qualified Xpaths.(Something that is definitely lacking in 7.x).

HTH

[eostic]

rameshrr3 is right.... it will be important to see you xpath.... Also...don't get fancy with mixed elements until you know that the basics are working..... the fully qualified example above is useful....but you might even want to see what you get without the qualifiers, just having a column for each attribute, and the "value" element.

Name xpath would be: BusinessEntity/Properties/Property/@Name
Value xpath would be: BusinessEntity/Properties/Property/Value/text()

[rameshrr3]

On a positive note im using certain Xpaths like the below
in datastage XML input stage and they work fine.

Code: Select all

/ns1:sophis-config/ns1:application-config[@deployed="true" and @name="Transaction Loader" and @id="3"]/ns1:configvalue/ns1:participantid/@value

Funnily such a complicated Xpath doesnt get imported via the XML metadata importer and i needed to 'handcode' it in the stage--> output--> column definitions table ( description field). Yes im talking about v 7.51A , not 8x.

[eostic]

Yeah...cool stuff in xpath that would be nice in the stage.....