Search code examples
xpathdatastage

XPath replace where tag is non-existent or empty

I am consuming the XML below into a database using DataStage. DataStage allows me to specify the XPATH to the specific fields to grab from xml and insert into the db table. As you can see from the xml below, some of the tags are empty or do not exist. I need an XPATH expression that can query if a tag is empty or non-existent and if it is either, then I want the XPATH to return a NULL so that the NULL can be inserted into the db table for that field e.g. on Entity_ID 2222 the Postal_Code is not present so I would want the Postal_Code field in the db table to be populated with a DB NULL. Equally, on Entity_ID 1111, where the City tag is present but empty, I would want a NULL in the City db table field. Any help is greatly appreciated. Thanks in advance.

<?xml version="1.0"?>
<NewDataSet>
<Entity_Data msdata:rowOrder="0" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" diffgr:id="Entity Data1" diffgr:hasChanges="inserted" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<Entity_ID>1111</Entity_ID>
<Name>EntityName</Name>
<Street>EntityStreet</Street>
<City/>
<State>EntityState</State>
<Postal_Code>12345</Postal_Code>
</Entity_Data>
<Entity_Data msdata:rowOrder="1" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" diffgr:id="Entity Data2" diffgr:hasChanges="inserted" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<Entity_ID>2222</Entity_ID>
<Name>EntityName</Name>
<Street>EntityStreet</Street>
<City>EntityCity</City>
<State>EntityState</State>
<Phone>555-555-5555</Phone>
</Entity_Data>
<Entity_Data msdata:rowOrder="4" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" diffgr:id="Entity Data5" diffgr:hasChanges="inserted" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<Entity_ID>3333</Entity_ID>
<Name>EntityName</Name>
<Street>EntityStreet</Street>
<City>EntityCity</City>
<State>EntityState</State>
<Postal_Code>22222</Postal_Code>
</Entity_Data>
</NewDataSet>
Solution

  • I got around this in DataStage by setting the XML Input stage 'nullable' property to 'yes' for all output rows (except the key being used for repetition).