Need a Query to Convert XML Column to Table values

Nov 2 2020 4:59 PM

Guys,

I have a below table

UserId

Name

CompanyInfo

1

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:ParentTransactionModel>

2

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:ParentTransactionModel>

Can u help me in building a Query from the source Table(above), so that I can insert the values in the target table(below) as below.

UserId

User Name

AgentNumber

AgentAdress

AgentCity

1

Johny

1017412

PO Box 19150

Agent City

2

Harry

1017413

PO Box 19151

Agent City1


Answers (1)