xml column to SQL table

Apr 8 2020 9:50 PM

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

UserId

Name

CompanyInfo

Johny

<XF1:ParentTransactionModel xmlns:XF1="http://schemas.testcontract.org/2004/07/Trade.Shared.ViewModels.Warehouse">

<XF1:TradeContractNumber xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

<XF1:AgentNumber>1017412</XF1:AgentNumber>

<XF1:AgentAddress1>PO Box 19150</XF1:AgentAddress1>

<XF1:AgentCity>Agent City</XF1:AgentCity>

<XF1:Companies>

<XF1:ChildCompanyModel>

<XF1:ChildAgentNumber>2017412</XF1:ChildAgentNumber>

<XF1:ChildAgentAddress>PO Box 29150</XF1:ChildAgentAddress>

<XF1:ChildAgentCity>Child City</XF1:ChildAgentCity>

</XF1:ChildCompanyModel>

</XF1:Companies>

<XF1:PremiumBaseDifference>2000</XF1:PremiumBaseDifference>

</XF1:ParentTransactionModel>

Harry

<XF1:ParentTransactionModel xmlns:XF1="http://schemas.testcontract.org/2004/07/Trade.Shared.ViewModels.Warehouse">

<XF1:TradeContractNumber xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

<XF1:AgentNumber>1017413</XF1:AgentNumber>

<XF1:AgentAddress1>PO Box 19151</XF1:AgentAddress1>

<XF1:AgentCity>Agent City1</XF1:AgentCity>

<XF1:Companies>

<XF1:ChildCompanyModel>

<XF1:ChildAgentNumber>2017413</XF1:ChildAgentNumber>

<XF1:ChildAgentAddress>PO Box 29152</XF1:ChildAgentAddress>

<XF1:ChildAgentCity>Child City1</XF1:ChildAgentCity>

</XF1:ChildCompanyModel>

</XF1:Companies>

<XF1:PremiumBaseDifference>2000</XF1:PremiumBaseDifference>

</XF1:ParentTransactionModel>

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

childTable

Answers (1)