XML File Import - Data Source Issue in defining the XPath

[avinashrvn]

Hello Experts,

Could you please check this. I had difficulties defining the Xpath in the data source to import the xml file. I need 3 fields to be created in the source table (asset_tag, dv_model_category, lastLogonAD)

This is the Sample XML file-

<?xml version="1.0" encoding="UTF-8"?>
<results preview="0">
<meta>
<fieldOrder>
<field>asset_tag</field>
<field>dv_model_category</field>
<field>lastLogonAD</field>
</fieldOrder>
</meta>
<messages>
<msg type="DEBUG">Configuration initialization for /opt/splunk/etc took 96ms when dispatching a search (search ID: 159XXXXXX.23XXX_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)</msg>
<msg type="DEBUG">search context: user="servicenowapi", app="search", bs-pathname="/opt/splunk/etc"</msg>
<msg type="INFO">Successfully read lookup file '/opt/splunk/etc/apps/XXXX_IT_Operations/lookups/servicenow_asset.csv'.</msg>
</messages>

<result offset="0">
<field k="asset_tag">
<value><text>9AG7XYZ</text></value>
</field>
<field k="dv_model_category">
<value><text>Computers</text></value>
</field>
<field k="lastLogonAD">
<value><text>12-31-2019</text></value>
</field>
</result>
<result offset="1">
<field k="asset_tag">
<value><text>046418543153</text></value>
</field>
<field k="dv_model_category">
<value><text>Computers</text></value>
</field>
<field k="lastLogonAD">
<value><text>12-31-1969</text></value>
</field>
</result>

<result offset="2">
<field k="asset_tag">
<value><text>046450243153</text></value>
</field>
<field k="dv_model_category">
<value><text>Computers</text></value>
</field>
<field k="lastLogonAD">
<value><text>12-31-1969</text></value>
</field>
</result>

[MrMuhammad · Accepted solution]

The xml you provided is missing </results> as a closing tag at the end. However, /results/result and //results are returning correct results.

You can try Xpath here. 

https://codebeautify.org/Xpath-Tester#

when you validate the correct Xpath by testing over the above link. you can use that in Data source and try deleting the staging table and run new data source so that it could pick right fields for staging table. 

Please mark this accepted & helpful if it answered your question.

Thanks & Regards,
Sharjeel

[Aoife Lucas]

Formatting XML always helps me (see below) to find the XPath to the value I want.  Now, depending on what you are going after you might be able to use //field[@k='asset_tag']/value/text/text(), which will give you an array of 3 values (1 for each time there is a k='asset_tag').

Tapadh leat,

Aoife

<?xml version="1.0" encoding="UTF-8"?>
<results preview="0">
	<meta>
		<fieldOrder>
			<field>asset_tag</field>
			<field>dv_model_category</field>
			<field>lastLogonAD</field>
		</fieldOrder>
	</meta>
	<messages>
		<msg type="DEBUG">Configuration initialization for /opt/splunk/etc took 96ms when dispatching a search (search ID: 159XXXXXX.23XXX_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)</msg>
		<msg type="DEBUG">search context: user="servicenowapi", app="search", bs-pathname="/opt/splunk/etc"</msg>
		<msg type="INFO">Successfully read lookup file '/opt/splunk/etc/apps/XXXX_IT_Operations/lookups/servicenow_asset.csv'.</msg>
	</messages>

	<result offset="0">
		<field k="asset_tag">
			<value>
				<text>9AG7XYZ</text>
			</value>
		</field>
		<field k="dv_model_category">
			<value>
				<text>Computers</text>
			</value>
		</field>
		<field k="lastLogonAD">
			<value>
				<text>12-31-2019</text>
			</value>
		</field>
	</result>
	<result offset="1">
		<field k="asset_tag">
			<value>
				<text>046418543153</text>
			</value>
		</field>
		<field k="dv_model_category">
			<value>
				<text>Computers</text>
			</value>
		</field>
		<field k="lastLogonAD">
			<value>
				<text>12-31-1969</text>
			</value>
		</field>
	</result>

	<result offset="2">
		<field k="asset_tag">
			<value>
				<text>046450243153</text>
			</value>
		</field>
		<field k="dv_model_category">
			<value>
				<text>Computers</text>
			</value>
		</field>
		<field k="lastLogonAD">
			<value>
				<text>12-31-1969</text>
			</value>
		</field>
	</result>
</results>