Loading

loding xml to a column

loding xml to a column

Mansoor Mohammed

6y744

XML_TABLE

Id	XML_DATA
1	India Agent1 PO Box 29150 Pakistan Bangladesh Agent2 SriLanka Agent3

I want to populate in the CompanyInfo table using CTE

WITH mycte AS (
SELECT
TRY_CAST([XML_DATA] AS XML)
FROM [XML_TABLE]
)

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jefferson S. Motta6y ago
Accepted answer
Your problem is that you city is a property name, not a value.

I can do that, but without access to the Database to work with the information is quite difficult.

Your select should by like that:
Select [Country], [City Code ???], [AgentName], [AgentAddress]
FROM (
WITH mycte AS (

SELECT

TRY_CAST([XML_DATA] AS XML)

FROM [XML_TABLE]

) )


t