locked
Can u plz help me Query RRS feed

  • Question

  • User-1738841732 posted

    I have 3 columns(with data types are STRING(varchar)) in a table by name bondId,bondName and BondXMLinfo


    below is the sample of one of the BondXMLinfo columns


    <PS1:ComapnyInfo xmlns:PS1="http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse"><PS1:AccountBillingNumber xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
    <PS1:AccountNumber>1017412</PS1:AccountNumber>
    <PS1:AgencyAddress1>PO Box 19150</PS1:AgencyAddress1>
    <PS1:AgencyAddress2>1333 S Rustle Rd.</PS1:AgencyAddress2>
    <PS1:AgencyCity>Spokane</PS1:AgencyCity>
    <PS1:AgencyCode>88229</PS1:AgencyCode>
    <PS1:AgencyCompany>Cochrane and Company</PS1:AgencyCompany>
    <PS1:ExpDate>2020-05-06T00:00:00</PS1:AgencyCountry>
    </PS1:CompanyInfo>

    I want the output as bondId, bondName and ExpDate from the BondXMLinfo columns

    Monday, June 29, 2020 8:12 PM

Answers

  • User452040443 posted

    Hi,

    mansooraabid, try something like this:

    WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse')
    
    select
        t.bondId,
        t.bondName,
        x.inf.value('(ExpDate)[1]', 'datetime2') as ExpDate
    from MyTable as t
    cross apply
    (
        select cast(t.BondXMLinfo as xml) as BondXMLinfo
    ) as ca
    cross apply ca.BondXMLinfo.nodes('/CompanyInfo') as x(inf)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2020 1:38 PM
  • User452040443 posted

    Try something like this:

    WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse')
    
    select
        t.bondId,
        t.bondName,
        max(x.inf.value('(ExpDate)[1]', 'datetime2')) as ExpDate
    from MyTable as t
    cross apply
    (
        select cast(t.BondXMLinfo as xml) as BondXMLinfo
    ) as ca
    cross apply ca.BondXMLinfo.nodes('/CompanyInfo') as x(inf)
    group by
        t.bondId,
        t.bondName
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2020 4:42 PM

All replies

  • User288213138 posted

    Hi mansooraabid,

    I have 3 columns(with data types are STRING(varchar)) in a table by name bondId,bondName and BondXMLinfo

    I want the output as bondId, bondName and ExpDate from the BondXMLinfo columns

    According to your description, I couldn’t understand your requirement clearly.

    Do you mean you want to query the values of the other 2 columns in the sql?

    If yes, then why don’t you use the query directly?

    SELECT bondName, bondId FROM table;

    If I misunderstand your requirement, please post more details information about your requirement.

    Best regards,

    Sam

    Tuesday, June 30, 2020 7:52 AM
  • User452040443 posted

    Hi,

    mansooraabid, try something like this:

    WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse')
    
    select
        t.bondId,
        t.bondName,
        x.inf.value('(ExpDate)[1]', 'datetime2') as ExpDate
    from MyTable as t
    cross apply
    (
        select cast(t.BondXMLinfo as xml) as BondXMLinfo
    ) as ca
    cross apply ca.BondXMLinfo.nodes('/CompanyInfo') as x(inf)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2020 1:38 PM
  • User-1738841732 posted

    Thnks, Now how do I get the Max of ExpDate(the latest one) for a particular BondId

    Tuesday, June 30, 2020 3:12 PM
  • User452040443 posted

    Try something like this:

    WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse')
    
    select
        t.bondId,
        t.bondName,
        max(x.inf.value('(ExpDate)[1]', 'datetime2')) as ExpDate
    from MyTable as t
    cross apply
    (
        select cast(t.BondXMLinfo as xml) as BondXMLinfo
    ) as ca
    cross apply ca.BondXMLinfo.nodes('/CompanyInfo') as x(inf)
    group by
        t.bondId,
        t.bondName
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2020 4:42 PM