how to select attribute value of a node in XQuery?
You should use data to pick attribute value:- for $c in /company/customers/customer return data($c/@cno)
xquery
How to query xml column in tsql
How about this? SELECT EventID, EventTime, AnnouncementValue = t1.EventXML.value(‘(/Event/Announcement/Value)[1]’, ‘decimal(10,2)’), AnnouncementDate = t1.EventXML.value(‘(/Event/Announcement/Date)[1]’, ‘date’) FROM dbo.T1 WHERE t1.EventXML.exist(‘/Event/Indicator/Name[text() = “GDP”]’) = 1 It will find all rows where the /Event/Indicator/Name equals GDP and then it will display the <Announcement>/<Value> and <Announcement>/<Date> for those rows. See SQLFiddle demo
XML query() works, value() requires singleton found xdt:untypedAtomic
You need to use this: SELECT x.requestpayload.value(‘declare namespace s=”http://blah.ca/api”; (/s:validate-student-request/s:student-id)[1]’, ‘int’) AS studentid FROM xoutput x You need to put your XPath in ( … ) and add a [1] to simply select the first value of that sequence.
How to get a particular attribute from XML element in SQL Server
Try using the .value function instead of .query: SELECT xmlCol.value(‘(/container/param[@name=”paramB”]/@value)[1]’, ‘varchar(50)’) FROM LogTable The XPath expression could potentially return a list of nodes, therefore you need to add a [1] to that potential list to tell SQL Server to use the first of those entries (and yes – that list is 1-based – not 0-based). … Read more
Concatenate multiple node values in xpath
Try this expression… string-join(//element3/(concat(element4/text(), ‘.’, element5/text())), “ ”)
SQL Server SELECT to JSON function
Starting from SQL Server 2016 you can use for json: declare @t table(id int, name nvarchar(max), active bit) insert @t values (1, ‘Bob Jones’, 1), (2, ‘John Smith’, 0) select id, name, active from @t for json auto With older versions of SQL Server you can use for xml path, e.g.: select ‘[‘ + STUFF(( … Read more
SQL: How can I get the value of an attribute in XML datatype?
Use XQuery: declare @xml xml=”<email> <account language=”en” /> </email>” select @xml.value(‘(/email/account/@language)[1]’, ‘nvarchar(max)’) declare @t table (m xml) insert @t values (‘<email><account language=”en” /></email>’), (‘<email><account language=”fr” /></email>’) select m.value(‘(/email/account/@language)[1]’, ‘nvarchar(max)’) from @t Output: en fr
Only select text directly in node, not in child nodes
In the provided XML document: <div id=”comment”> <div class=”title”>Editor’s Description</div> <div class=”changed”>Last updated: </div> <br class=”clear”> Lorem ipsum dolor sit amet. </div> the top element /div has 4 children nodes that are text nodes. The first three of these four text-node children are whitespace-only. The last of these 4 text-node children is the one that … Read more
Difference between XPath, XQuery and XPointer
Wikipedia is a good place to start for questions like this. Generally, XPath is a language used to succinctly pinpoint exact XML nodes in a DOM. XQuery is a superset of XPath that also provides FLWOR syntax, which is SQL-like. Finally, XPointer includes XPath, but also provides a simpler position-based addressing scheme. Of course, you … Read more
Using XPath, How do I select a node based on its text content and value of an attribute?
Generally I would consider the use of an unprefixed // as a bad smell in an XPath. Try this:- /DocText/WithQuads/Page/Word[text()=’July’ and Quad/P1/@X > 90] Your problem is that you use the //P1[@X < 90] which starts back at the beginning of the document and starts hunting any P1 hence it will always be true. Similarly … Read more