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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
18 Points
41 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
3370 Points
1409 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
Participant
1140 Points
299 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
18 Points
41 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
1140 Points
299 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