SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – OpenXML Options – Day 35 of 35

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?

1. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
   FROM Buildings
   WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day
2. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
   FROM Buildings
   WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]’) = 1
3. 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)