February 15, 2010 at 2:34 am
I have a column in which each row has XML file and XML file has so many NOdes .
My query is to "pass the XML node as parameter and get the respective node value from each row
February 15, 2010 at 10:11 am
Hmm, I'm not sure that there is a good way to do this.
It sounds like someone wants an "XML Cursor" and there really isn't such a thing in T-SQL. An "XML Node" is not a datatype in SQL Server, it's just a temporal query context into an XML object and cannot be used outside of a single query, AFAIK. The closest that you could come would probably be some XML fragment string or XQuery instance-type string that could be used to represent a particular node in an XML object. And I don't see that as being either easy or efficient.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 15, 2010 at 10:39 am
Are you sure you're not looking for something like the xml.nodes() method from the SQLXML functionality? This does allow you to pass in a single XML datatype, and split it up into multiple XML "sub-items" (again, not specifically treated as XML Nodes, just as another XML fragment).
You could run a query that looks something like
select a2.b3.query('.')
from @myXML.nodes('/MyXPathStatementToTheNodes/.') as a2(b3)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2010 at 12:22 pm
If your question has not been answered yet by either Barry or Matt then it would really help if you could provide a short sample of what you're trying to do:
Sample data, search value and expected result.
February 15, 2010 at 9:18 pm
Thanks Barry and Matt for the reply
I think I was not explained the problem clearly in above text .
now I am going to explain it by example :
<FormHeader> <txtFieldSubmitURLH>http://localhost/Publish/</txtFieldSubmitURLH>
<chkViewH>0</chkViewH>
<chkPublishH>1</chkPublishH>
</FormHeader>
above is my XML file , I have table(tblStudentXmlData) which has column(strXMLString) in which each row has this XML file with their respective data .
SELECT
strXMLString.value('(//txtFieldSubmitURLH)[1]','varchar(255)')
FROM tblStudentXmlData
above Query is running perfectly fine,but I want to pass the node(txtFieldSubmitURLH) as parameter like below .
DECLARE @strFieldName varchar(200)
SET @strFieldName = 'txtFieldSubmitURLH'
SELECT
strXMLString.value('(//@strFieldName)[1]','varchar(255)')
FROM tblStudentXmlData
SELECT
strXMLString.value('(sql:variable("@strFieldName"))[1]','varchar(255)')
FROM tblStudentXmlData
I tried like above but nothing was working
I hope , I have explained my query better than before
February 15, 2010 at 10:43 pm
You're looking to do some form of Dynamic SQL. In other words - you will have to build the SQL statement in a varchar variable, then use EXEC or sp_executeSQL to run it. You cannot unfortunately use variables in that particular way to dynamically pick the XML node name.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 16, 2010 at 10:18 am
And just to warn you ahead of time: If you are looking to write this as a T-SQL Function, be aware that they do not allow dynamic SQL. Kind of a catch-22... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 18, 2010 at 11:46 pm
Hi Barry ,
can you explain me by example ,
how to resolve the above problem by Function .
Thanks
March 19, 2010 at 2:04 pm
What you could use is a query like the one below.
The disadvantage is that it will query all nodes on each and every level within the file.
What I'm trying to say: I wouldn't recommend using it agains a large XML file.
DECLARE @xml XML
DECLARE @strFieldName VARCHAR(200)
SET @strFieldName = 'txtFieldSubmitURLH'
SET @xml='<FormHeader> <txtFieldSubmitURLH>http://localhost/Publish/</txtFieldSubmitURLH>
<chkViewH>0</chkViewH>
<chkPublishH>1</chkPublishH>
</FormHeader>'
SELECT
y.value('local-name(.)', 'VARCHAR(50)') AS FieldName,
y.value('(.)', 'VARCHAR(50)') AS val
FROM @xml.nodes('//FormHeader') t(c)
CROSS APPLY
t.c.nodes('*') x(y)
WHERE y.value('local-name(.)', 'VARCHAR(50)') = @strFieldName
/* result set
FieldName val
txtFieldSubmitURLH http://localhost/Publish/
*/
March 19, 2010 at 2:52 pm
The Local Name option is the only one I know of for this that would work in a query. I've used it, and it does work, but it can be quite slow on a large table. Performance on it can be improved with a Path XML Index, but don't expect it to be fast if the table is large or the XML is substantial.
- 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
March 22, 2010 at 10:47 pm
Thanks SSCrazy
its working fine
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply