Vairable in XQuery

• ganeshmuthuvelu

  SSCrazy

  Points: 2177

  More actions

  May 20, 2008 at 1:12 pm

  #207416

  Hello,

  I have a XML as below:

  declare @doc2 xml

  SET @doc2 = '<?xml version="1.0" ?>

  <p1: Domains_and_Emails xmlns:p1="LDAP">

  <Domain>

  <UnixEmail>

  <uid>info</uid>

  <Password>xyz</Password>

  <EmailAccount>info@xyz.com</EmailAccount>

  </UnixEmail>

  </Domain>

  </p1: Domains_and_Emails>'

  and I am using the xml.nodes function to get the XML at the required position as below:

  SELECT

  tab.col.value('uid[1]','VARCHAR(20)')AS uid

  FROM @doc2.nodes('declare namespace p1="LDAP"; //p1: Domains_and_Emails/Domain[1]/UnixEmail[1]') tab(col)

  In the above example, how would I replace the static literal "//p1: Domains_and_Emails/Domain[1]/UnixEmail[1]" with a variable?. basically, I need to pass a value for that string via a variable..

  Can someone help?

  Thanks,

  Ganesh

• GSquared

  SSC Guru

  Points: 260824

  More actions

  May 20, 2008 at 1:55 pm

  #818203

  I could be wrong, but I think that'll have to be dynamic SQL. Build the select command as a string and execute it.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon