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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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)

Joes 2 Pros, SQL Scripts
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Preparing XML in Memory – Day 34 of 35
Next Post
SQL SERVER – Programming and Development – Book Available for SQL Server Certification

Related Posts

44 Comments. Leave new

  • Option will produce the desired results.

    Country: United States

    Reply
  • Sowmiya Duraisamy
    September 5, 2011 3:03 am

    Option 2 is correct.
    SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    If “Wednesday” exist then the opening hours will be retrieved and displayed.

    I’m from USA

    Reply
  • 2.SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Gordon Kane
    Allen TX
    USA

    Reply
    • Gordon – We have a position open in Plano, TX for a Data Analyst. If you are interested, could you contact me -Thank you very much

      Reply
  • Hi Pinal,

    Challenge:
    Question 35
    You have a table named Buildings that has an XML column named StoreHours. This column contains the opening and closing times:

    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

    Correct Answer:
    The correct choice is # 2, with some slight SQL syntax modifications.

    Explanation:
    Besides replacing the single and double quotation marks (they were italics), the query still doesn’t return any data unless you remove the space in
    ” Wednesday” (use “Wednesday”). Here is what my final query looked like”:

    SELECT StoreName, convert(varchar(5), StoreHours.value(‘/hours[1]/@open’,’time’), 8) + ‘ AM’ as ‘Opening Time on Wednesdays’
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]’) = 1

    Here is the data I retrieved (after creating a Buildings table and inserting StoreName (varchar) and Opening Times (XML data)):

    StoreName Opening Time on Wednesdays

    Super Market 08:00 AM
    Sports Arena 10:00 AM
    Office 08:00 AM

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply
  • With as little as I know about SQL and XML, I would think #2 would be the answer.

    SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Unfortunately with it being a weekend I do not have access to sql mgmt studio to check it. I would think the space before Wed would create a problem. The third one just doesn’t look right to me and the first one might work but I’m not sure about the where clause. I know how exists works and I feel better about the second option.

    USA

    Reply
  • Hi Bill,

    Make sure to create different store hours for every day (say, put some exotic values for Wednesday) and re-test. You’ll notice, that the second option (with the modifications) will return the first record and not the Wednesday open hours. The reason is that ‘exist’ option checks for existence of the option (in this case it checks that Wednesday exists and it’s true), but it doesn’t select that option.

    So, the correct answer to this question – none of the options as presented returns the correct result.

    Reply
    • Hi Naomi,

      Thanks for the input.

      You are correct regarding that EXIST doesn’t show all the data.

      While I didn’t just see 8 am in my result set, I clearly wasn’t retrieving all of the Wednesday opening times.

      Thanks for the insight.

      Bill

      Reply
  • Kalyanasundaram.K
    September 5, 2011 9:56 am

    Correct Answer : Option 2

    2 ) SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Its bit complex to choose correct answer for Day35 Blog Quiz Series.

    Chennai, Tamilnadu, India

    Reply
  • I will use “SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings”
    Option 2 is the correct Answer.

    Thanks Pinal for this series. I read all the questions and also tried to give answers. I won a Book also.:)

    Rajneesh Verma
    (INDIA)

    Reply
  • Rajesh Mohanrangan
    September 5, 2011 1:06 pm

    Answer: option 2

    Regards
    Rajesh
    From india

    Reply
  • Correct Answer option #2

    SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Nikhildas
    Cochin
    INDIA

    Reply
  • Option 2

    2.SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Country:India

    Reply
  • ANS : None

    I agree with Naomi
    Ans given by Naomi using Cross Apply which is working.

    IF I run the third option I get the same error as Naomi
    Msg 2396, Level 16, State 1, Line 35
    XQuery [@Buildings.StoreHours.query()]: Attribute may not appear outside of an element

    Error Solution
    ——————
    Use the data() function to query attributes and resolve this problem

    DECLARE @Buildings table(StoreName varchar(10), StoreHours XML)
    INSERT INTO @Buildings( StoreName, StoreHours )
    VALUES ( ‘A’,


    )

    INSERT INTO @Buildings( StoreName, StoreHours )
    VALUES ( ‘B’,


    )
    INSERT INTO @Buildings( StoreName, StoreHours )
    VALUES ( ‘C’,


    )

    –SELECT * FROM @Buildings
    declare @Day varchar(20)
    set @Day = ‘Wednesday’

    SELECT Storename, StoreHours.query(‘data(/hours[@dayofWeek=”Wednesday”]/@open)’) AS ‘WedOpenTime’
    FROM @Buildings

    StoreName WedOpenTime
    A 6:00
    B 7:00
    C 8:00

    Mitesh Modi
    (India)

    Reply
  • The correct answer is option 2

    SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=” Wednesday”]‘) = 1

    Mahmad Khoja
    India

    Reply
  • Option -2 is the Correct answer

    Thanks
    Narendra(India)

    Reply
  • option 2 is correct answer.

    Vaishali
    Country of residence: Hyderabad, India

    Reply
  • Tricky one. Hope this helps me in future

    Reply
  • Kevin Scharnhorst
    September 7, 2011 10:10 pm

    Correct answer is option #2

    Kevin Scharnhorst
    United States

    Reply
  • sharath molagavalli
    March 19, 2013 5:26 pm

    Can somebody explain the difference between flag 0 argument & flag 1 argument
    , of OPENXML function

    Reply

Leave a Reply