XML : selectnodes distinct attributes with xpath 1.0 and XML

Sorry for the long post......

I am finding it difficult to retrieve the distinct values from a node with my XML file and the code which i have developed so far.

  Function Design_TEST()    For Each Folder In xmlDoc.SelectNodes("//FOLDER")    FolderName = Folder.getAttribute("NAME")                For Each Mapping In Folder.SelectNodes("MAPPING")              MappingName = Mapping.getAttribute("NAME")                r = Range_Find_Method()              mainWorkBook.Sheets(S4).Range("A" & r + 2).Value = "Mapping Name"              mainWorkBook.Sheets(S4).Range("B" & r + 2).Value = MappingName                      For Each Instance In Mapping.SelectNodes("INSTANCE[@TRANSFORMATION_TYPE='Source Qualifier']")                      InstanceName = Instance.getAttribute("NAME")                            For Each Connector In Mapping.SelectNodes("CONNECTOR[@FROMINSTANCE='" & InstanceName & "']")                              ConnectToInstance = Connector.getAttribute("TOINSTANCE")                              ConnectFromInstance = InstanceName                                r = Range_Find_Method()                                mainWorkBook.Sheets(S4).Range("A" & r + 1).Value = ConnectFromInstance                              mainWorkBook.Sheets(S4).Range("B" & r + 1).Value = ConnectToInstance                          Next 'End of For loop for Connector                  Next ' End of For loop for Instance                Next 'End of For loop for Mapping  Next 'End of For loop for Folder      End Function

The output i am getting is:

Mapping Name|m_load_map1

But the ouput i need is :

Mapping Name|m_load_map1
SQ_DW_LOC_ID|exp_DW_LOC_ID
SQ_YIELDPOOL|exp_YIELDPOOL

I went through some old posts and understood that i need to use "following-sibling" or "preceding::row". But i am not able to understand how to fit this to my requirement. Any suggestions to achieve the above output? Would be really great if i can get any inputs!!!

Below is my input XML file:

  <?xml version="1.0" encoding="ISO-8859-1"?>  <!DOCTYPE POWERMART SYSTEM "powrmart.dtd">  <POWERMART CREATION_DATE="10/07/2016 14:19:24" REPOSITORY_VERSION="181.90">  <REPOSITORY NAME="infa_edw_dev_new" VERSION="181" CODEPAGE="Latin1" DATABASETYPE="Oracle">  <FOLDER NAME="DEV_rocky" GROUP="" OWNER="Administrator" SHARED="NOTSHARED" DESCRIPTION="" PERMISSIONS="rwx---r--" UUID="2cc91af7-8250-4616-9861-d9e2c441a5c7">  <MAPPING DESCRIPTION ="" ISVALID ="YES" NAME ="m_load_map1" OBJECTVERSION ="1" VERSIONNUMBER ="1">          <INSTANCE DESCRIPTION ="" NAME ="exp_DW_LOC_ID" REUSABLE ="NO" TRANSFORMATION_NAME ="exp_DW_LOC_ID" TRANSFORMATION_TYPE ="Expression" TYPE ="TRANSFORMATION"/>          <INSTANCE DESCRIPTION ="" NAME ="exp_YIELDPOOL" REUSABLE ="NO" TRANSFORMATION_NAME ="exp_YIELDPOOL" TRANSFORMATION_TYPE ="Expression" TYPE ="TRANSFORMATION"/>          <INSTANCE DESCRIPTION ="" NAME ="SQ_DW_LOC_ID" REUSABLE ="NO" TRANSFORMATION_NAME ="SQ_DW_LOC_ID" TRANSFORMATION_TYPE ="Source Qualifier" TYPE ="TRANSFORMATION">              <ASSOCIATED_SOURCE_INSTANCE NAME ="STATIONS"/>              <ASSOCIATED_SOURCE_INSTANCE NAME ="YIELDLOCATION"/>              <ASSOCIATED_SOURCE_INSTANCE NAME ="YIELDPOOL2"/>              <ASSOCIATED_SOURCE_INSTANCE NAME ="LOCATIONBRAND"/>          </INSTANCE>          <INSTANCE DESCRIPTION ="" NAME ="SQ_YIELDPOOL" REUSABLE ="NO" TRANSFORMATION_NAME ="SQ_YIELDPOOL" TRANSFORMATION_TYPE ="Source Qualifier" TYPE ="TRANSFORMATION">          <ASSOCIATED_SOURCE_INSTANCE NAME ="YIELDPOOL"/>          </INSTANCE>          <CONNECTOR FROMFIELD ="YIELDLOCATION" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="YIELDLOCATION" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="STATION" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="STATION" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="YIELDPOOL" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="YIELDPOOL" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="LOCATIONDESCRIPTION" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="LOCATIONDESCRIPTION" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="SMALLLOCATIONDESC" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="SMALLLOCATIONDESC" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="MODELTYPE" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="MODELTYPE" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="POOLDESCRIPTION" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="POOLDESCRIPTION" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="DIVISION" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="DIVISION" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="BRAND" FROMINSTANCE ="SQ_DW_LOC_ID" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="BRAND" TOINSTANCE ="exp_DW_LOC_ID" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="YIELDPOOL" FROMINSTANCE ="SQ_YIELDPOOL" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="YIELDPOOL" TOINSTANCE ="exp_YIELDPOOL" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="POOLDESCRIPTION" FROMINSTANCE ="SQ_YIELDPOOL" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="POOLDESCRIPTION" TOINSTANCE ="exp_YIELDPOOL" TOINSTANCETYPE ="Expression"/>          <CONNECTOR FROMFIELD ="DIVISION" FROMINSTANCE ="SQ_YIELDPOOL" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="DIVISION" TOINSTANCE ="exp_YIELDPOOL" TOINSTANCETYPE ="Expression"/>  </MAPPING>   </FOLDER>  </REPOSITORY>  </POWERMART>

XML : selectnodes distinct attributes with xpath 1.0 and XML

No comments:

Post a Comment