One of the most common problem SQL Server developers face while dealing with XML is related to writing the correct XPath expression to read a specific value from an XML document. I usually get a lot of questions by email, on my blog or in the forums which looks like the following:
“I have the following XML document and I am trying to read the value from xyz node. When I run my query, I get a NULL value”
My friend Jacob Sebastian (SQL Server MVP) has written excellent article on the subject SELECT * FROM XML, I strongly recommend to either bookmark it and read it before continuing further in this article.
In most cases, I have seen that the problem was due to an incorrect XPath expression. XPath expressions are not complicated at all, but they need a close attention to get them right. Assume that we have the following XML fragment.
Looking at the structure of the XML document, it is quite easy to figure out the XPath expression pointing to each element and attribute. The following illustration shows how to do this.
One of the simplest ways is to identify the nodes with their position as given in the above illustration. A more detailed listing is given below.
The above example also used the “position” of the elements to uniquely identify them. In real life you might need to have more complex matching criteria such as the “email of the Employee element whose id is 10001”. The following example shows how to apply this type of filters.
/Employees/Employee[@id="1001"]/Email
In most cases, you will be able to easily build your XPath expressions. However, if you find it difficult, you can take help from my helper function given here. This function allows you to run ‘blind’ queries on the XML document very similar to the ‘select * from table’ queries that we usually run on unknown tables.
For example, if you would like to quickly examine the above XML document and see the elements, attributes and their XPath expression, you can execute something like the following:
Make sure that you create the function XMLTable() using the script given in the above URL.
Once you have the output of the function, you can copy the XPath expressions from the results and use in your Queries. For example, if you are looking for the email address of Pinal, you can just copy the expression from row 8 (highlighted in the image given above) and use in your query as:
SELECT @x.value(‘Employees[1]/Employee[2]/Email[1]‘,’VARCHAR(50)’)
I hope you will find this post interesting and the XMLTable() function might help you to solve some of the XML querying problems you may face in your SQL Server Journey. If you have got any question about XML in general, or about this function in particular, please feel free to post them on the XML forum and I will try my best to help you out.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Very nice article. I was always scared using XML. But, after reading this, I think it is very easy and handy. Thanks for sharing such a nice thing.
Very Nice article………Pinal can u explain about the what are the diff types of Database Documentation,how it is useful? etc…and How a Start up firm implement these types of documentation based on Agile model.I am Expecting an Interesting Article from you………
Very nicely written, This really helps out with XPATH, one of the challenging things i have faced with XML is more of the mechanics of XPATH, this example makes it easier.
[...] We also received couple of excellent guest blog posts as well. Imran Mohammed – SQL SERVER – Shrinking NDF and MDF Files – Readers’ Opinion and Jacob Sebastian SQLAuthority News – Guest Post – SELECT * FROM XML – Jacob Sebastian . [...]
Very nicely written. I’m wonderin how the function can be modified to take namespaces into account?
I’m looking for
SELECT * FROM @Xml
into one result row, one column per attribute,
with the attribute name as the column name,
and the attribute value as the value in the one row.
I’m not there yet,
but this T-Sql:
declare @idoc int
exec sp_xml_preparedocument @idoc OUTPUT,
”;
WITH EdgeTable AS
(
SELECT *
FROM OPENXML (@idoc, ‘/*’)
)
SELECT e1.localname Attribute,
e2.text Value
FROM EdgeTable e1 join
EdgeTable e2 on e1.id = e2.parentid
where e2.localname = ‘#text’;
EXEC sp_xml_removedocument @idoc
returns this:
Attribute Value
——— —–
Name Jones
Phone 123
And I don’t know the schema.
I’m trying to make a generic routine that can be run on any xml. All I want is the data.
I’m trying to get it into one row though,
and so far, no luck.
Any help would be appreciated.
A solution not involving OPENXML would be better also.
I’ll post if I solve it first.
Did you get a solution?
my xml got chopped out of my sample code.
exec sp_xml_preparedocument @idoc OUTPUT,
‘<Customer Name=”Jones” Phone=”123″ />’;
Also,
my result data has 2 columns and looks like this:
Attribute|Value
Name |Jones
Phone |123
Sorry about the 2nd post.
Very useful article Pinal :)
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#137-139 SELECT * FROM XML Shredding XML Validate an XML document in TSQL using XSD Simple Example of Reading XML File Using [...]
Hi Pinal,
Please see the query below,
declare @tstTable table(txt xml)
insert @tstTable
select ‘
X
Y
A
B
‘
select * from @tstTable
I need to get the output as following, i need to use the xml.Modify
I am expecting your reply.Plese help.
X Y
A B
declare @tstTable table(txt xml)
insert @tstTable
select ‘<root>
<parent>
<firstname>X</firstname>
<lastname>Y</lastname>
</parent>
<child>
<firstname>A</firstname>
<lastname>B</lastname>
</child>
</root>’
select * from @tstTable
I need to get the output as following, i need to use the xml.Modify
I am expecting your reply.Plese help.
<root>
<parent>
<fullname>X Y</fullname>
</parent>
<child>
<fullname>A B</fullname>
</child>
</root>
If node randomly change then how get the node value