I have a pyspark dataframe having a string column named "xml", but the column has nested xml data inside it.
This is the dataframe.
df = spark.createDataFrame([['<?xml version="1.0" encoding="UTF-8"?> <XLif xmlns=""><UserAuthResponse xmlns=""><TransResult><ResultCode tc="1">AA</ResultCode><ConfirmationID>123</ConfirmationID></TransResult></UserAuthResponse><XLifR xmlns="" PrimaryObjectID="Holding@75895"><TransRefGUID>123</TransRefGUID><TransMode tc="2">AAA</TransMode><TransResult><ResultCode tc="2">AAA</ResultCode><ConfirmationID>123</ConfirmationID><ResultInfo><ResultInfoCode tc="999">Unknown reason</ResultInfoCode><ResultInfoDesc>ABC</ResultInfoDesc></ResultInfo></TransResult><OL><Holding id="Holding@75895"><HoldingTypeCode tc="2">AAA</HoldingTypeCode><AsOfDate>2022-09-22</AsOfDate><RestrictionCode tc="1">No Restriction</RestrictionCode><Head1 id="Head1_75896"><Val1>AAA</Val1><Val2>AA</Val2><Val3 tc="1">AA.</Val3></Head1><Loan id="Loan_75898"><LoanBalance>0</LoanBalance><LoanPaymentAmt>0</LoanPaymentAmt><LoanInterestRate>0</LoanInterestRate></Loan></Holding><FormInstance id="FormInstance_75897"><FormName>5</FormName><PolNumber>123</PolNumber></FormInstance></OL></XLifR></XLif>']], ['xml'])
Output of df.printSchema() is below:
root
|-- xml: string (nullable = true)
Expectation is to create three new dataframe columns with name Val1, Val2, Val3 having their corresponding values. These are present at this path inside xml:
Val1:
/XLif/XLifR/OL/Holding/Head1/Val1
Val2:
/XLif/XLifR/OL/Holding/Head1/Val2
Val3:
/XLif/XLifR/OL/Holding/Head1/Val3
Any leads will be helpful. Thank you!!!