locked
Convert XML Column to Table values RRS feed

  • Question

  • User-1738841732 posted

    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

     

     

    Monday, November 2, 2020 10:58 PM

Answers

  • User452040443 posted

    Try:

    from MyTable as t
    cross apply
    (
        select cast(t.CompanyInfo as xml) as CompanyInfoXml
    ) as c
    cross apply c.CompanyInfoXml.nodes('/XF1:ParentTransactionModel') as x(inf)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 3, 2020 3:03 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    WITH XMLNAMESPACES('http://schemas.testcontract.org/2004/07/Trade.Shared.ViewModels.Warehouse' as XF1)
    
    select
        t.UserId,
        t.Name,
        x.inf.value('(XF1:AgentNumber)[1]', 'int') as AgentNumber,
        x.inf.value('(XF1:AgentAddress1)[1]', 'varchar(20)') as AgentAddress,
        x.inf.value('(XF1:AgentCity)[1]', 'varchar(50)') as AgentCity
    from MyTable as t
    cross apply t.CompanyInfo.nodes('/XF1:ParentTransactionModel') as x(inf)

    Hope this help

    Tuesday, November 3, 2020 2:14 PM
  • User-1738841732 posted

    My source table column CompanyInfo dataType is varcharMax, so the above query is not executing :(

    Can you please rebuilt the query?

    Tuesday, November 3, 2020 2:43 PM
  • User452040443 posted

    Try:

    from MyTable as t
    cross apply
    (
        select cast(t.CompanyInfo as xml) as CompanyInfoXml
    ) as c
    cross apply c.CompanyInfoXml.nodes('/XF1:ParentTransactionModel') as x(inf)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 3, 2020 3:03 PM
  • User-1738841732 posted

    Thnk U!

    It worked

    Tuesday, November 3, 2020 3:19 PM
  • User-1738841732 posted

    Thnks

    Tuesday, November 3, 2020 3:54 PM