Post
Ask Question
Mansoor Mohammed

Mansoor Mohammed

  • NA
  • 126
  • 0

loding xml to a column

May 19 2020 11:24 AM

XML_TABLE

Id

XML_DATA

1

<ParentTransactionModel>
<Country>India</Country>
<State>
<Mumbai>
<AgentName>Agent1</AgentName>
<AgentAddress>PO Box 29150</AgentAddress>
</Mumbai>
<State>
<Country>Pakistan</Country>
<Country>Bangladesh</Country>
<State>
<Dhaka>
<AgentName>Agent2</AgentName>
</Dhaka>
<State>
<Country>SriLanka</Country>
<State>
<Columbo>
<AgentName>Agent3</AgentName>
</Columbo>
<State>
</ParentTransactionModel>

I want to populate in the CompanyInfo table using CTE

  1. WITH mycte AS (  
  2. SELECT  
  3. TRY_CAST([XML_DATA] AS XML)   
  4. FROM [XML_TABLE]  
  5. )  
Conditions:

if the AgentName exists, then the City Code should appear along with Name and Adress 

CompanyINFO 

Country

City Code

Agent Name

Address

India

M

Agent1

PO Box 29150

Bangladesh

D

Agent2

  

SriLanka

C

Agent3

  

Answers (1)