Need help iterating thru XML field using XQuery

  • I have an XML column in a SQL Server 2K8 database that houses test data. Each row in the table represents a unique test and the XML has a "question" property for every question in each test. I need some way of retrieving every question for each test in a tabular format. I've tried a "for" function using xquery: Select TestXML.query('for $question in xml/content/group/questionset/question

    return string($question)')

    From TestTable

    This gets the data I want but returns the questions in one long string back-to-back(question 1,question 2,question 3,etc.). What I'm looking for is to have the data return in a tabular format or some way that I can display the data for the questions for each test in a report.

    (question 1

    question 2

    question 3)

    Does anyone know of a way to do this? I greatly appreciate any help.

  • Can you post the DDL for the table and some sample data? See the links in my signature for how - to.

    With the DDL and test data you can get suggestions that will more likely work. I think you want something like this (stolen almost directly from BOL - Books on line):

    -- Create the table.

    CREATE TABLE T (

    ProductID int primary key,

    CatalogDescription xml)

    GO

    -- Insert sample data. Replace the curly braces ({}) with greater than ('>') and less than ('<') characters

    -- the forum doesn't display them so I replaced them with the curly braces.

    INSERT INTO T values(1,'{Products}

    {ProductDescription ProductID="1" ProductName="SomeName" }Name{/ProductDescription}

    {ProductDescription ProductID="2" ProductName="SomeName2" }Name2{/ProductDescription}

    {/Products}')

    SELECT ProductID,

    product.value('.', 'VARCHAR(100)') AS NAME

    FROM

    T CROSS APPLY

    CatalogDescription.nodes('Products/ProductDescription') AS T1(product)

    DROP TABLE T

  • Thanks, it worked just fine. I'm still very new to using xml in sql so this helped out tremendously. Thanks again.

  • I have encountered a new issue that I think may be due to poorly formatted xml but I'm not sure. I have this snippet of code:

    which holds the text for each question and the possible answers and responses for each question. I want to be able to retrieve only the question text and answers in a table like so:

    Create Table T(ID INT Primary Key, Question varchar(255), Answer varchar(255),Response varchar(255))

    with the data looking like this:

    ID Question Answer Response

    1 Question 1 Yes Correct

    2 Question 1 No Incorrect

    3 Question 2 Yes Incorrect

    4 Question 2 No Correct

    The problem I'm having is that when I try to pull the text element for the text of the question, I pull the answer and response along with it and I don't know if I'm doing something wrong or if it's a result of the xml being formatted incorrectly.

    The data looks like this:

    ID Question

    1 Question 1YesCorrect

    2 Question 1NoIncorrect

    3 Question 2YesIncorrect

    4 Question 2NoCorrect

    Any suggestions?

  • XML disappears when you post it in the forum. Works best if you save it in a text file and attach that to the post.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Sorry, here's the code snippet I meant to include in the previous post(with {} replacing <>):

    {questionset}

    {question vo="" id="1" returnOnIncorrect="false"}Will this work?{answer id="1" correct="0"}{input}Maybe{/input}{output vo=""}That is correct.{/output}{/answer}{/question}

    {question vo="" id="2" returnOnIncorrect="false"}What time is it?{answer id="4" correct="0"}{input}Gametime{/input}{output vo=""}That is incorrect.{/output}{/answer}{/question}

    {question vo="" id="3" returnOnIncorrect="false"}Who Won Super Bowl XLIII?{answer id="7" correct="0"}{input}Cardinals{/input}{output vo=""}That is incorrect.{/output}{/answer}{/question}

    {/questionset}

  • Here's the xml attached in a text file for better viewing.

  • Yes it is poorly formed xml. I think in order to be formed the way you want the XML should be like this:

    {questionset}

    {question vo="" id="1" returnOnIncorrect="false"}

    {questiontext}Will this work?{/questiontext}

    {answer id="1" correct="0"}

    {input}Maybe{/input}

    {output vo=""}That is correct.{/output}

    {/answer}

    {answer id="2" correct="0"}

    {input}Yes{/input}

    {output vo=""}That is correct.{/output}

    {/answer}

    {answer id="3" correct="0"}

    {input}NO{/input}

    {output vo=""}That is incorrect.{/output}

    {/answer}

    {/question}

    {/questionset}

    The issue right now is that all the elements are inside the question element are all considered the text of the question element. I just added the questiontext element.

    I'm not an XQuery expert so now that you would have multiple answer elements I'm not sure the best way to get that information in the format you need especially if you have a varying number of answer elements.

  • I was afraid that was the case. Thanks for all your help! I appreciate it.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply