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
"Ganesh Muthuvelu" wrote in message news:CCB34C12-0D42-445E-A85F-F63656A889F1@microsoft.com... > 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 > > As far as I understaqnd you can't, the standard workaround in to construct the SQL statement dynamically and execute using sp_ExecuteSQL. You may wish to try microsoft.public.sqlserver.xml for these types of questions. -- Joe Fawcett (MVP - XML) http://joe.fawcett.name
