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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

  2. 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

  3. 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.

  4. 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

  5. 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

  6. 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.

  7. 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

  8. 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

  9. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  10. 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

  11. 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

  12. 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

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

    Gordon Kane
    Allen TX
    USA

    • 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

  14. 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

  15. 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

  16. 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.

    • 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

  17. 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

  18. 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)

  19. Correct Answer option #2

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

    Nikhildas
    Cochin
    INDIA

  20. Option 2

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

    Country:India

  21. 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)

  22. 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

  23. As this was the last question – it was posted as indeed tricky question. We have received two correct answer. I must congratulate winners for the same.

    A.)None is the correct answer.

    Winner from USANaomi

    Winner from India: Mitesh Modi

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

  24. Pingback: SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

  25. Pingback: SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s