Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
OpenXML Options
The last posts introduced us to the OpenXML function. We learned the two required parameters for this function are the handle (which must be in the form of an integer) and the rowpattern (to know what part of the XML has your data).
The OpenXML function offers some helpful options for querying. This post will explore the two main syntaxes for rowpattern recursion (searching lower levels) and column pattern options (searching one level higher).
Rowpattern Recursion
Suppose you are a manager at a company. You work for a supervisor and likely also have people who report to you. Even at the largest company, every person who works there is an employee, from the CEO on down to managers and individual contributors at all levels. Our recursion example will include an XML document having many element levels named “Emp.” (short for Employee)
It’s common to see elements inside of elements with the same name. Sometimes the name of the element is more important than its exact level. In other words, you could see one employee level nested beneath another employee level, and so forth and you want to see all employees. You don’t care how far down thoes levels go but want to pull out all employees. In our example, we will see how level recursion can help us locate the data we need within XML tags having the same name.
Let’s create a simple XML document showing a small organization having one boss named Tom. The top Boss Tom will have two employees, Dick and Harry.
Add the code to declare the two variables (the integer to give us the document handle and the xml variable to store our XML document), store the XML document into the variable @Doc, prepare the document, and display the handle. As you can see from the figure below our document handle is 1.
Declare the variable to access our XML document and set it equal to our document handle (which is our “claim ticket” number, 1). Then add the Select statement to query the result set of the OpenXML function. Our rowpattern parameter (‘/Root/Emp’) specifies that we want only the the top-level <Emp> element. Only one employee is at the top so we only get Tom. We want to see the next employee level, which contains Dick and Harry.
Now let’s modify our code to see the next level down. The child-level element (rowpattern ‘/Root/Emp/Emp’) contains Dick and Harry but not Tom.
Look at the string parameter of the OpenXML line. A slight modification to our rowpattern parameter specifies that we want to pull data from every level, at or beneath the Root, and having an XML tag named Emp. This is an example of using a rowpattern’s relative navigation capability. Thus far our row level specifications have been absolute – our OpenXML queries have explicitly named each level and instructed SQL Server to pull data from that level. Now lets see some of the tools SQL Server makes available for relative navigation of our XML data. We want to see data from all levels named “Emp.” The Figure below uses a double forward slash before the Emp. This means any Emp tag below Root will be searched. The result is you get Tom, Dick, and Harry.
Relative Level
We are already familiar with the concept of defining entities or positions in a relative way – that is, as they relate to other entities or positions rather than explicitly calling them by their unique names or fixed positions. In conversation, my dad often refers to his “two younger sons,” rather than explicitly using the names “Jeff” and “Rick.” At work I sometimes get visitors who come to my door when they are actually looking for the fellow who occupied my office up until a month ago. Rather than giving them his new office number, it is easier to direct them to take the elevator up to the next floor and then to look for the first door on the right. (I’ve found when I told them, “He’s moved to room 401”, I’ve always needed to give them the friendly directions anyway. So after the first day, I just skipped the explicit name 401 and gave them the relative location.)
Throughout the Joes 2 Pros posts, we have seen the importance of variables to make our T-SQL code more flexible and reusable instead of explicitly hardcoding each value. The OpenXML function similarly offers us the option of pulling data in a relative fashion. In other words, instead of explicitly naming the element levels where we want SQL Server to navigate and pull data, our code can provide rowpattern or column pattern (colpattern) instructions relative to a specified context.
What if you were an executive and you wanted to see of list of all employees who were exactly 2 levels below you and what it their name and their bosses name? In our next example, the rowpattern option will help us pull data from another level relative to our current position. Recall our example of a large organization with many employee levels. You can use rowpattern’s ability to query relatively and find the managers who are two levels above a certain manager. Perhaps you’ve been asked to compile a list of all the employees who are three levels beneath your manager.
We now will modify our current code to include a column specifying each employee’s boss. Our goal is to see the boss’ name listed alongside each employee. Note that the name of Tom’s boss will appear as NULL, since Tom is the highest level boss in the organization.
The new trick here will be to add ‘../’ to the front of the column pattern (colpattern) parameter, e.g. ‘../@User’, which tells SQL Server to search the level above. In other words, we want SQL Server to go up one level from the current context and retrieve the User attribute from that level. The syntax ‘../../@User’ would tell SQL Server to go up two levels and retrieve the User attribute.
DECLARE @iDoc INT
SET @iDoc=1
SELECT *
FROM Openxml(@idoc,'/Root//Emp')
WITH (EmployeeName VARCHAR(MAX) '@User',
BossName VARCHAR(MAX) '../@User')
Since our rowpattern itself ‘/Root//Emp’ is relative, it will search for data at the root level and every child level below. If the ‘/Root//Emp’ rowpattern finds an employee at the fifth level, then the ‘../@User’ colpattern specifier will go up one level (i.e., up to the fourth level) to search for corresponding data. For example, when the rowpattern instruction ‘/Root//Emp’ locates an Emp level, the colpattern ‘@User’ will find the first User attribute (Dick) at the Emp level. The colpattern ‘../@User’ instructs SQL Server to retrieve the User attribute (Tom) from the level above Dick’s level.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.
Question 35
You have a table named Buildings that has an XML column named StoreHours. This column contains the opening and closing times:
<hours dayofWeek=“Monday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Tuesday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Wednesday” open=“8:00” closed= “18:00”/>
…
<hours dayofWeek=“Saturday” open=“9:00” closed= “17:00”/>
You need to write a query that returns a list of Buildings and their opening time for Wednesday. Which code segment should you use?
- SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day - SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]’) = 1 - SELECT Storename, StoreHours.query(‘/hours[@dayofWeek=”Wednesday”]/@open’)
FROM Buildings
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
44 Comments. Leave new
This is quite an interesting and a complex question. Also, this question does not correspond to the material explained in this article. Without trying it I was thinking the correct option is 2, however, I decided to try it out in order to be absolutely certain.
I tried the following query:
use tempdb
–drop table Buildings
create table Buildings (StoreName varchar(100), StoreHours XML)
insert into Buildings values (‘Macy’, ‘
‘),
(‘Barns & Noble’, ‘
‘)
–You need to write a query that returns a list of Buildings and their opening time for Wednesday. Which code segment should you use?
declare @Day varchar(10)
set @Day = ‘Wednesday’
SELECT StoreName, StoreHours.value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day
SELECT StoreName, StoreHours.value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]’) = 1
SELECT Storename, StoreHours.query(‘/hours[@dayofWeek=”Wednesday”]/@open’)
FROM Buildings
—————————-
The last query produced this error
Msg 2396, Level 16, State 1, Line 10
XQuery [Buildings.StoreHours.query()]: Attribute may not appear outside of an element
And the first 2 worked (well, I declared @Day variable for the first), but didn’t return any result.
———————–
So, I am not sure what is wrong, but the answer to this question is none of the options is correct (at least, according to my test above).
Thanks for great series.
I am from USA
Correct answer is No.3, because we need to get a list of Buildings and their opening time for Wednesday. Options 1 and 2 use the WHERE clause, but the column containing the needed information is in XML format, so a special query must be done to get that information.
Rene Castro
El Salvador
Option 2 is the answer as the other 2 are syntactically wrong.
Sathya, Chennai, India
Corect answer is option 2.
Thank You
Uday
Option 2 is the True Answer as we need to get the list of Buildings which are open on wednesday. And option 2 is returning the same with compairing with where clause.
Thanks
Anand M Tiwari.
Here is the query that works for the required task (I used CROSS APPLY method):
declare @Day varchar(10)
set @Day = ‘Wednesday’
SELECT StoreName,x.value(‘@open[1]’,’time’)
FROM Buildings
CROSS APPLY StoreHours.nodes(‘/hours’) StoreH(x)
WHERE x.value(‘@dayofWeek[1]’,’varchar(20)’) = @Day
———-
So, option 1 can be made a workable query if we apply the above modifications.
I am from USA
This is another variation of the correct query returning the desired output
SELECT StoreName, x.value(‘@open’,’time’)
FROM Buildings
CROSS APPLY StoreHours.nodes(‘/hours[@dayofWeek=”Wednesday”]’) StoreH(x)
Again I used the nodes syntax
I am from USA
We should use Query3, which uses XQuery to jump onto the interested record and giving back the interested attribute (@Open) right there itself.
Thanks for sharing it.
-M
My postal address is ::
[address removed]
Hope to get your books for free very soon.
Thanks again.
Hi Munish,
If you are winner you will for sure receive the book.Due to public forum, your address has been removed.
Correct Answer is # 2.
SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘)
Option 2 is the True Answer as we need to get the list of Buildings which are open on wednesday.
As per explanation by “Naomi” with different comments. I am little confused with the answers.
Dear Naomi, its good, you are trying with different options to test all the answers,
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
Hi Sir,
Option 2 seems to be correct as we have to get the values only for wednesday
SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1
Regards,
P.Anish Shenoy,
INDIA, BANGALORE, KARNATAKA
Hi,
Correct Answer is 2.
I am from Ahmedabad,Gujarat,India.
The correct Option for the above question is Option # 2, because it returns a list of Buildings and their opening time for Wednesday.
OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
The WITH clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName. If the optional WITH clause is not specified, the results are returned in an edge table format. Edge tables represent the fine-grained XML document structure (such as element/attribute names, the document hierarchy, the namespaces, PIs, and son on) in a single table.
In retrieving the data, rowpattern is used to identify the nodes in the XML document that determine the rows. Additionally, rowpattern is expressed in the XPath pattern language that is used in the MSXML XPath implementation. For example, if the pattern ends in an element or an attribute, a row is created for each element or attribute node that is selected by rowpattern.
The flags value provides default mapping. If no ColPattern is specified in the SchemaDeclaration, the mapping specified in flags is assumed. The flags value is ignored if ColPattern is specified in SchemaDeclaration. The specified ColPattern determines the mapping, attribute-centric or element-centric, and also the behavior in dealing with overflow and unconsumed data.
A. Executing a simple SELECT statement with OPENXML
The XML document in this example is made up of the , , and elements. The OPENXML statement retrieves customer information in a two-column rowset, CustomerID and ContactName, from the XML document.
First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. This document handle is passed to OPENXML.
The OPENXML statement illustrates the following:
rowpattern (/ROOT/Customer) identifies the nodes to process.
The flags parameter value is set to 1 and indicates attribute-centric mapping. As a result, the XML attributes map to the columns in the rowset defined in SchemaDeclaration.
In SchemaDeclaration, in the WITH clause, the specified ColName values match the corresponding XML attribute names. Therefore, the ColPattern parameter is not specified in SchemaDeclaration.
The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.
DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N’
‘
— Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
— Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, ‘/ROOT/Customer’,1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @DocHandle
This is the result:
CustomerID ContactName
———- ——————–
VINET Paul Henriot
LILAS Carlos Gonzlez
——————————————————————–
Dilip Kumar Jena
Country: INIDA
Correct option should be #2.
SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘)
New Delhi
India
Ans is 2.
SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1
I guess it will be write .
Partha,
India
Option 2 seems to be the correct answer.
(Sale, Nigeria)
correct answer is: option 2
Thanks,
Basavaraj
India
Option 2 correct answer.
2.SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1
Finding the StoreHours
Richardson, TEXAS, USA
the correct option is option 2 i.e.
2.SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1
India
correct ans is 2.
SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1
Haresh Ambaliya (India)