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
Finding node order in XML document in SQL Server
You can emulate the position() function by counting the number of sibling nodes preceding each node: SELECT code = value.value(‘@code’, ‘int’), parent_code = value.value(‘../@code’, ‘int’), ord = value.value(‘for $i in . return count(../*[. << $i]) + 1’, ‘int’) FROM @Xml.nodes(‘//value’) AS T(value) Here is the result set: code parent_code ord —- ———– — 1 NULL … Read more
Concatenate multiple node values in xpath
Try this expression… string-join(//element3/(concat(element4/text(), ‘.’, element5/text())), “ ”)
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
Concatenate grouped rows
Use: select t.id, sum(t.price) , stuff(( select distinct ‘,’ + cast(t2.ServiceID as varchar(max)) from @t t2 where t2.id = t.id for xml path(”) ), 1, 1, ”) from @t t group by t.id Output: ———– ——————— ——————— 1 40,00 11,12 2 120,00 11
Execute a XQuery with PHP
PHP does not have any native or common XML parsers that support XQuery (If I’m wrong, someone let me know). It does however have two pretty standard extensions that handle XPath queries. I personally think simplexml is the better of the two. You would simply use: $xml = new simplexml($some_xml_string); $xpath_results = $xml -> Xpath(“//a/b”); … 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
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
Is there a JSON equivalent of XQuery/XPath?
Yup, it’s called JSONPath: It’s also integrated into DOJO.