query XMLType

• peter_opglab

  Valued Member

  Points: 52

  More actions

  January 20, 2012 at 7:49 am

  #249996

  Hi

  i have a tabel a lot of field AND with a XML field contining a xml. The xml look like following

  <root>

  <AList>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name</F2>

  </DelivN1>

  </DeliveryNameAddresses>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name</F2>

  </DelivN1>

  <DelivN3>

  <SID>N1</SID>

  <F1>SF</F1>

  <FA>Adres</FA>

  </DelivN3>

  </DeliveryNameAddresses>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name2</F2>

  </DelivN1>

  <DelivN3>

  <SID>N1</SID>

  <F1>SF</F1>

  <FA>Adres2</FA>

  </DelivN3>

  </DeliveryNameAddresses>

  </AList>

  </root>

  how can i select records from the table where the xmlfield tag AList.DeliveryNameAddresses.DelivN1.F1 = SF

  knowing that there are more the 1 DeliveryNameAddresses in the xml

  thx

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  January 20, 2012 at 8:12 am

  #1436521

  Use the XML exist method

  DECLARE @t TABLE (xmlfield XML)

  INSERT INTO @t(xmlfield)

  VALUES('

  <root>

  <AList>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name</F2>

  </DelivN1>

  </DeliveryNameAddresses>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name</F2>

  </DelivN1>

  <DelivN3>

  <SID>N1</SID>

  <F1>SF</F1>

  <FA>Adres</FA>

  </DelivN3>

  </DeliveryNameAddresses>

  <DeliveryNameAddresses>

  <DelivN1>

  <SID>N1</SID>

  <F1>SF</F1>

  <F2>Name2</F2>

  </DelivN1>

  <DelivN3>

  <SID>N1</SID>

  <F1>SF</F1>

  <FA>Adres2</FA>

  </DelivN3>

  </DeliveryNameAddresses>

  </AList>

  </root>

  ')

  SELECT *

  FROM @t

  WHERE xmlfield.exist('/root/AList/DeliveryNameAddresses/DelivN1/F1/text()[.="SF"]') = 1

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537