I have 3 columns(with data types are STRING(varchar)) in a table by name bondId,bondName and BondXMLinfo
mansooraabid
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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)
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
Member
16 Points
34 Posts
Can u plz help me Query
Jun 29, 2020 08:12 PM|mansooraabid|LINK
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
Contributor
3160 Points
1353 Posts
Re: Can u plz help me Query
Jun 30, 2020 07:52 AM|samwu|LINK
Hi mansooraabid,
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?
If I misunderstand your requirement, please post more details information about your requirement.
Best regards,
Sam
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Participant
960 Points
241 Posts
Re: Can u plz help me Query
Jun 30, 2020 01:38 PM|imapsp|LINK
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
Member
16 Points
34 Posts
Re: Can u plz help me Query
Jun 30, 2020 03:12 PM|mansooraabid|LINK
Thnks, Now how do I get the Max of ExpDate(the latest one) for a particular BondId
Participant
960 Points
241 Posts
Re: Can u plz help me Query
Jun 30, 2020 04:42 PM|imapsp|LINK
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.bondNameHope this help