Can u plz help me Query[Answered] RSS

• • Reply
  mansooraabid

  Member

  17 Points

  37 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

• • Reply
  samwu

  Contributor

  3370 Points

  1409 Posts

  Re: Can u plz help me Query

  Jun 30, 2020 07:52 AM|samwu|LINK

  Hi mansooraabid,

  mansooraabid

  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.
  
• • Reply
  imapsp

  Participant

  1030 Points

  267 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

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  mansooraabid

  Member

  17 Points

  37 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

• • Reply
  imapsp

  Participant

  1030 Points

  267 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.bondName
  

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa