Loading

xml column to SQL table

xml column to SQL table

Mansoor Mohammed

6y877

I have the below Sourcetable in my DB, where the CompanyInfo is defined as varchar(Max).

UserId

Name

CompanyInfo

1

Johny

1017412

PO Box 19150

Agent City

2017412

PO Box 29150

Child City

2000

2

Harry

1017413

PO Box 19151

Agent City1

2017413

PO Box 29152

Child City1

2000

I created two table parentTable and childTable

Can u help me in building a Query from the sourceTable, so that I can insert the values as below.

parentTable

UserId	User Name	AgentNumber	AgentAdress	AgentCity
1	Johny	1017412	PO Box 19150	Agent City
2	Harry	1017413	PO Box 19151	Agent City1

childTable

UserId	User Name	ChildAgentNumber	ChildAgentAdress	ChildAgentCity
1	Johny	2017412	PO Box 29150	Child City
2	Harry	2017413	PO Box 29152	Child City1

1 Reply

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

Jignesh Kumar6y ago
Accepted answer
Hi,

You can create your model with all properties and list of child company details. Then you need to read as xml and desirialize your query response. Please use below link,
https://blog.sqlauthority.com/2012/04/27/sql-server-introduction-to-discovering-xml-data-type-methods-a-primer/
https://www.sqlshack.com/working-with-xml-data-in-sql-server/