XML Parsing in Snowflake with sub nodes

Question

I am trying to parse below XML.

<row id="C2786">
  <custID>C2786</custID>
  <name>NEW Pensions Ltd </name>
  <RegName>NEW Pensions Ltd</RegName>
  <ADDress>22 Broomfield Road</ADDress>
  <ADDress m="2">Birmingham EAST</ADDress>
  <ADDress m="3">Birmingham</ADDress>
  <ADDress m="3">B1 1AY</ADDress>
  <ADDress m="3">United Kingdom</ADDress>
  <PhoneNo m="2">+44 20 6776577</PhoneNo>
  <PhoneNo m="2" s="2">+44 20 4576787</PhoneNo>
  <CustType>Corporate</CustType>
</row>

I need data in below format.

Output 1

CustID	CustName	Addressline1	Addressline2	Addressline3	PostCode	Country
C2786	New Pensions Lts	22 Broomfield Road	Birmingham East	Birmingham	B1 1AY	United Kingdom

Output 2

Output 2

CustID	CustName	Phone No1	Phone No 2
C2786	New Pensions Ltd	+44 20 6776577	+44 20 4576787

Code I am using

Select
     XMLGET( XMLrecord, 'custID' ):"$"::STRING AS CustID,
     XMLGET( XMLrecord, 'name' ):"$"::STRING AS CustName,
     XMLGET( XMLrecord, 'Address' ):"$"::STRING AS AddressLine1,
     XMLGET( XMLrecord, 'Address' ):"$m=2"::STRING AS AddressLine2,
     XMLGET( XMLrecord, 'Address' ):"$m=3"::STRING AS City,
     XMLGET( XMLrecord, 'Address' ):"$m-4"::STRING AS PostCode,
     XMLGET( XMLrecord, 'Address' ):"$m=5"::STRING AS Country,
  
  FROM XMLTable;

  Select
       XMLGET( XMLrecord, 'custID' ):"$"::STRING AS CustID,
       XMLGET( XMLrecord, 'name' ):"$"::STRING AS CustName,
       XMLGET( XMLrecord, 'PhoneNo' ):"$m=2"::STRING AS Landline,
       XMLGET( XMLrecord, 'PhoneNo' ):"$m=2 s=2"::STRING AS Mobile,

    FROM XMLTable;

Please help. Thanks

Answer 1

this should work:

Select
     GET(XMLGET(XMLrecord, 'custID'), '$')::string  AS CustID,
     GET(XMLGET(XMLrecord, 'name'), '$')::STRING AS CustName,
     GET(XMLGET(XMLrecord, 'ADDress', 0), '$')::STRING AS AddressLine1,
     GET(XMLGET(XMLrecord, 'ADDress', 1), '$')::STRING AS AddressLine2,
     GET(XMLGET(XMLrecord, 'ADDress', 2), '$')::STRING AS City,
     GET(XMLGET(XMLrecord, 'ADDress', 3), '$')::STRING AS PostCode,
     GET(XMLGET(XMLrecord, 'ADDress', 4), '$')::STRING AS Country
  FROM XMLTable;