-
I found this question in this SSC forum thread requesting help to shape the XML document. Here is the input XML document. <Root> <Student>Jhon </Student> <Student> Luka </Student> <Post>1</Post> <Post>2</Post> </Root> The expected output is...
-
I got a question in my personal forum this morning requesting help to read values from an XML column. My first reaction was “Well, there is an XQuery lab demonstrating this!”. However, after reviewing the existing XQuery labs, I realized there are no posts demonstrating this. Here is a simple example...
-
Introduction This is the fifth and final post in a series of five on Using TVP’s and XML to Transport Relational Data . In this post we’ll take a look at some tips and tricks used to increase OpenXML and XQuery performance. Refresher The purpose behind this series of posts is to provide .Net Developers...
-
Introduction This is the third post in a series of five on Using TVP’s and XML to Transport Relational Data . In this post we’ll take a look at how to use XQuery within Transact-SQL to shred XML data. We’ll also look at how .Net Developers can use XQuery to reduce the number of SQL Server calls required...
-
In the past I have published a few queries that allows you to query SQL Server Reporting Services database – to retrieve specific information about various reports deployed in the Reporting Server Instance. Recently, I got a question in my ASK forum who wanted to identify all the reports that use a specific...
-
I saw this question on the forum today and after writing an example that demonstrates this, I thought of including it as part of the XQuery Labs . Here is the sample XML we need to process. <userdata pageid="page9" annotationSetId="80"> <notes /> <notes> <note...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
12-14-2010
Filed under:
Filed under: XQuery-Functions, XQuery-Labs, XML, XQuery, TSQL, BRH, XQuery Tutorials, XQuery Lab, XQuery Functions, #XML, #XQUERY, SQL Server - XQuery, #TSQL, #SQL Server, XQuery Training
-
This post aims to be a quick reference source for the beginners and shows a few simple SELECT examples. Reading attributes from an XML variable DECLARE @x XML SET @x = '<author fname="Michael" lname="Howard" />' SELECT @x.value('(/author/@fname)[1]', 'VARCHAR...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
07-01-2010
Filed under:
Filed under: XQuery-Functions, XQuery-Labs, XML, XQuery, SQLSERVER, BRH, XQuery Tutorials, XQuery Lab, XQuery Functions, xquery-lab, #XML, #XQUERY, #TSQL, SQL Server XQuery, XQuery in TSQL, XQuery Training
-
We have seen several examples of writing recursive queries in the earlier blog posts. For a quick recap, you can find some of those posts in the list given below. TSQL Lab 10 - Performing recursive updates in SQL Server TSQL Lab 11 - Writing a recursive procedure to update the count of child items under...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
06-29-2010
Filed under:
Filed under: XML, XQuery, BRH, XQuery Functions, xquery labs, #XML, #XQUERY, SQL Server - XQuery, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial
-
This installment of XQuery Lab shows how to execute dynamic queries that contain OPENXML() calls. We will see examples that use EXEC() and sp_executesql. Using EXEC() Let us start with EXEC(). DECLARE @hdoc INT DECLARE @xml VARCHAR(MAX) SELECT @xml = ' <Employees> <Employee ID="1001"...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
06-24-2010
Filed under:
Filed under: XML, XQuery, BRH, XQuery Functions, xquery labs, #XML, #XQUERY, SQL Server - XQuery, #SQLSERVER, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial
-
A few weeks back, I published a function named XMLTable() that helps to query XML documents easily. The idea behind the function is to be able to blindly query XML documents just like you do a ‘SELECT *’ on a relational table. Not only that this function helps you to query XML documents easily, it also...
-
In the previous post , we saw a basic example that reads information from an XML document using OPENXML(). In this post, we will focus on reading information from XML documents having namespace declarations. Here is a simple example: DECLARE @x VARCHAR(8000) SET @x = '<a:authors xmlns:a="http...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
06-22-2010
Filed under:
Filed under: XML, XQuery, BRH, XQuery Functions, xquery labs, #XML, #XQUERY, SQL Server - XQuery, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial
-
This post intends to help you get started with OPENXML() function. OPENXML() lets you shred an XML document or fragment into a result set. Though OPENXML() has got no direct relationship with XQuery, I thought of including it in the XQuery Labs series for completeness. Just like XQuery, OPENXML() can...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
06-17-2010
Filed under:
Filed under: XML, XQuery, TSQL, SQLSERVER, BRH, XQuery Functions, xquery labs, sp_xml_preparedocument, OPENXML, Database, DBA, #XML, #XQUERY, sp_xml_removedocument, #SQLSERVER, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial
-
I think it will be quite common that you try to insert a UNICODE value into an XML document and then you see that the INSERT did not work correctly because the value you find within the XML document is slightly different from what you inserted. I got a similar question recently and thought of adding...
-
Most people find it very difficult to deal with XML documents in TSQL as there is no way to run a ‘blind’ SELECT * query on an XML document to get a quick view of the content stored in it. A “select TOP N *” query can quickly give you a few records from the table which will give you an idea about the...
Posted to
Jacob's Blog
by
Jacob Sebastian
on
05-30-2010
Filed under:
Filed under: XQuery-Labs, XML, XQuery, BRH, XQuery Functions, XML Toolkit, XML Library, xquery labs, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial
-
In my previous blog entry , I described in detail how you could use the FOR XML clause to create a column with a comma-separated list of entries acquired from rows in a table somewhere. In this article, we will do the opposite… We will take a comma-separated list and “shred” the individual items into...