November 10, 2011 at 3:53 am
Hi.
I am trying to solve a problem where I have a table containing different XML documents. I want to retrieve a value based on an XPath expression from another column. This forces me to create the query completely dynamic when I do not know the content of the XML or the Xpath.
What I am trying to do is:
Declare @searchParam varhar(100)
SET @searchParam = Select XpathExpr FROM Table1 WHERE something...
Declare @fileTextXMLvarhar(Max)
SET @fileTextXML = Select XMLDocument FROM Table1 WHERE something...
SELECT @fileTextXML.value('sql:variable("@searchParam")', 'varchar(500)') AS result
This example will only retreive the Xpath expression and not the value.
I know that the value works if you hardcode the Xpath expression or if you use the parameter as a part of an expression, but I cannot use any of that unfortunately.
The question is if this is possible or if it some manageable workarounds?
November 10, 2011 at 4:33 am
sql:variable cannot be used for an XPath expression - it's really a bind variable for specific XML element or attribute values.
However, what if you created some dynamic SQL with the variable, then executed it instead?
This blog discusses this option in more detail.
November 10, 2011 at 5:12 am
Thanks for this input. I know that executing a created SQL statement might solve the problem, but this query is a bit complex and I hoped to avoid using this method. But as you stated that the “sql:variable it's really a bind variable for specific XML element or attribute values” it might be the only way to go.
November 10, 2011 at 7:06 am
Yep, you'll have to use dynamic SQL for this. No way around 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply