Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Accessing XMl as part of a SQL Statement 1

Status
Not open for further replies.
pabowen

pabowen

Programmer
Nov 6, 2002
95
US
Hello everyone,

I have run into a couple of problems that are baffling me. I am able to select XML data if the variable @MyXML is populated using text and a SET command, but if I populate @MyXML using a select statement it will not allow me to parse the variable and return results.

For Example:
Code: 
DECLARE
@MySql VARCHAR(2000) 
 
SET
@MySql=' 
<OptionList>
<Option isCorrect="1" Shuffle="0" WeightPercent="100">
<text ident="1">A.Trader:Market</text>
<feedback></feedback>
</Option>
<Option isCorrect="0" Shuffle="0" WeightPercent="0">
<text ident="2">B.Doctor:Patent</text>
<feedback></feedback>
</Option>
<Option isCorrect="1" Shuffle="0" WeightPercent="100">
<text ident="3">C.Teacher:School</text>
<feedback></feedback>
</Option>
<Option isCorrect="0" Shuffle="0" WeightPercent="0">
<text ident="4">D.Athelete:Olympics</text>
<feedback></feedback>
</Option>
</OptionList>'
 
DECLARE
@MyXml XML 
SET
@MyXml = CAST(@MySql AS XML) 
 
SELECT
Question.Answer.value('(text/@ident)[1]', 'VARCHAR(50)') AS Ident, 
Question.Answer.value('text[1]', 'VARCHAR(50)') AS Name, 
Question.Answer.value('@WeightPercent', 'INT') AS WeightPercent, 
Question.Answer.value('@Shuffle', 'INT') AS Shuffle, 
Question.Answer.value('@isCorrect', 'INT') AS IsCorrect 
FROM
@MyXml.nodes('/OptionList/Option') AS Question(Answer)

Works really well, but similar logic that populates the variables differently will not work, for example:
Code: 
DECLARE
@MySql VARCHAR(2000) 
 
set @MySql = (select OptionData from QA_Question where Question_PK = 1)

DECLARE @MyXml XML ;

SELECT
Question.Answer.value('(text/@ident)[1]', 'VARCHAR(50)') AS Ident, 
Question.Answer.value('text[1]', 'VARCHAR(50)') AS Name, 
Question.Answer.value('@WeightPercent', 'INT') AS WeightPercent, 
Question.Answer.value('@Shuffle', 'INT') AS Shuffle, 
Question.Answer.value('@isCorrect', 'INT') AS IsCorrect 
FROM
@MyXml.nodes('/OptionList/Option') AS Question(Answer)

Returns no data and no errors.

Any thoughts? Stuck on this item.

Thanks,
Patrick
 
Sort by date Sort by votes
I don't see where you initialize the @MyXML value.

Code: 
DECLARE
@MySql VARCHAR(2000) 
 
set @MySql = (select OptionData from QA_Question where Question_PK = 1)

DECLARE @MyXml XML ;

[!]Set @MyXml = CONVERT(XML, @MySql)[/!]

SELECT
Question.Answer.value('(text/@ident)[1]', 'VARCHAR(50)') AS Ident, 
Question.Answer.value('text[1]', 'VARCHAR(50)') AS Name, 
Question.Answer.value('@WeightPercent', 'INT') AS WeightPercent, 
Question.Answer.value('@Shuffle', 'INT') AS Shuffle, 
Question.Answer.value('@isCorrect', 'INT') AS IsCorrect 
FROM
@MyXml.nodes('/OptionList/Option') AS Question(Answer)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George, I apparently missed that when I typed it in here, my apologies. I utilized your example however and I am still returning no values.

If I simply perform Select * from @MyXML then it shows the values, but not if I try to grab node values.

Any thoughts?
 
Upvote 0 Downvote
What is the data type of the OptionData column in QA_Question?

Code: 
Select DATA_TYPE 
From   INFORMATION_SCHEMA.COLUMNS 
Where  TABLE_NAME = 'QA_QUESTION'
       And COLUMN_NAME = 'OptionData'

What version of SQL are you using?

Code: 
Select SERVERPROPERTY('ProductVersion')

What value do you have in your XML?

Code: 
Select OptionData From QA_Question Where Question_PK = 1

Please run all 3 queries and post the result here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George,

Here are the answers you requested.

Datatype = ntext

Version=9.00.4035.00

Value=
Code: 
<optionList><option shuffle="0" isCorrect="0"><text ident="1">&lt;B&gt;A.The brakes and steering failed &lt;B&gt;</text><feedback>"&lt;style=""font:Arial;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="0"><text ident="2">&lt;B&gt;B.and the bus ran down the hill&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="1"><text ident="3">&lt;B&gt;C.without anyone being able control it&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="0"><text ident="4">&lt;B&gt;D.No error&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option></optionList>

To recreate the issue with correct values, here is a table creation and population statement.
Code: 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QA_Question](
	[Question_PK] [int] NOT NULL,
	[OptionData] [ntext] NOT NULL
)

GO
SET ANSI_PADDING OFF

INSERT INTO QA_Question (Question_PK, OptionData)
VALUES (1,'<optionList><option shuffle="0" isCorrect="0"><text ident="1">&lt;B&gt;A.The brakes and steering failed &lt;B&gt;</text><feedback>"&lt;style=""font:Arial;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="0"><text ident="2">&lt;B&gt;B.and the bus ran down the hill&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="1"><text ident="3">&lt;B&gt;C.without anyone being able control it&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option><option shuffle="0" isCorrect="0"><text ident="4">&lt;B&gt;D.No error&lt;B&gt;</text><feedback>"&lt;style=""font:Verdana;size:10;""&gt;&lt;/style&gt;"</feedback></option></optionList>');
INSERT INTO QA_Question (Question_PK, OptionData)
VALUES (2,'<optionList><option isCaseSensitive="0"><text ident="1">A.Was just swallowing</text><text ident="2">B.Swallowed</text><text ident="3">C.Had just swallowed</text><text ident="4">D.Just swallowed</text><feedback></feedback></option></optionList>');
INSERT INTO QA_Question (Question_PK, OptionData)
VALUES (3,'<optionList><option isCorrect="1" shuffle="0" weightPercent="100"><text ident="1">A.Trader:Market</text><feedback></feedback></option><option isCorrect="0" shuffle="0" weightPercent="0"><text ident="2">B.Doctor:Patient</text><feedback></feedback></option><option isCorrect="1" shuffle="0" weightPercent="100"><text ident="3">C.Teacher:School</text><feedback></feedback></option><option isCorrect="0" shuffle="0" weightPercent="0"><text ident="4">D.Athlete:Olympics</text><feedback></feedback></option></optionList>');
INSERT INTO QA_Question (Question_PK, OptionData)
VALUES (4,'<optionList><option isCaseSensitive="0"><text ident="1">A.Flattening</text><text ident="2">B.Striking</text><text ident="3">C.Hammering</text><text ident="4">D.Thrashing</text><feedback></feedback></option></optionList>');
INSERT INTO QA_Question (Question_PK, OptionData)
VALUES (5,'<optionList><option shuffle="0" isCorrect="0"><text ident="1">A.To see signs of plague</text><feedback></feedback></option><option shuffle="0" isCorrect="0"><text ident="2">B.To get a bad small of a dead rat</text><feedback></feedback></option><option shuffle="0" isCorrect="1"><text ident="3">C.To suspect foul dealings</text><feedback></feedback></option><option shuffle="0" isCorrect="0"><text ident="4">D.To be in a bad mood</text><feedback></feedback></option><option shuffle="0" isCorrect="0"><text ident="5">E.None of these</text><feedback></feedback></option></optionList>');

Thanks,
Patrick
 
Upvote 0 Downvote
Are you ready to kick yourself?

your XML data...
[tt]<[!]o[/!]ptionList><[!]o[/!]ption shuffle="0"[/tt]

Your nodes query...
[tt]@MyXml.nodes('/[!]O[/!]ptionList/[!]O[/!]ption') AS Question(Answer)[/tt]

Since XML is case sensitive, your nodes are not found. Try this:

Code: 
DECLARE
@MySql VARCHAR(2000) 
 
set @MySql = (select OptionData from QA_Question where Question_PK = 1)

DECLARE @MyXml XML ;

Set @MyXml = CONVERT(XML, @MySql)

SELECT
Question.Answer.value('(text/@ident)[1]', 'VARCHAR(50)') AS Ident, 
Question.Answer.value('text[1]', 'VARCHAR(50)') AS Name, 
Question.Answer.value('@WeightPercent', 'INT') AS WeightPercent, 
Question.Answer.value('@Shuffle', 'INT') AS Shuffle, 
Question.Answer.value('@isCorrect', 'INT') AS IsCorrect 
FROM
@MyXml.nodes('/[!]o[/!]ptionList/[!]o[/!]ption') AS Question(Answer)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George, thank you incredibly... and Yes I am now kicking myself. :)

For the life of me i could not figure out why this was occuring.

A follow on Question to this, if I may. When I return the results for this query, I need to associate the Question_PK Column from the originating table so I can match it up.

Is there anyway to associate the Question_PK, which is outside of the XML, with the data that is in the XML?

So the desired result would be:
Code: 
1    1	A.Trader:Market	        NULL	NULL	1
1    2	B.Doctor:Patient	NULL	NULL	0
1    3	C.Teacher:School	NULL	NULL	1
1    4	D.Athlete:Olympics	NULL	NULL	0

Thank you again,
Patrick
 
Upvote 0 Downvote
Do you mean something like this:

Code: 
SELECT Question_PK,
       Question.Answer.value('(text/@ident)[1]', 'VARCHAR(50)') AS Ident, 
       Question.Answer.value('text[1]', 'VARCHAR(50)') AS Name, 
       Question.Answer.value('@WeightPercent', 'INT') AS WeightPercent, 
       Question.Answer.value('@Shuffle', 'INT') AS Shuffle, 
       Question.Answer.value('@isCorrect', 'INT') AS IsCorrect 
FROM   (
         Select Convert(XML, OptionData) As xmlOptionData, 
                Question_PK 
         From   QA_Question) As A
       Cross Apply xmlOptionData.nodes('/optionList/option') AS Question(Answer)
Where  Question_PK = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George,

Thank you, your help was invaluable and you did solve the problem perfectly.

I greatly appreciate everything.

Patrick
 
Upvote 0 Downvote
You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
557
pjw001
pjw001
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
751
mikrom
mikrom
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
529
Volvere
V
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
261
grnzbra
grnzbra
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
334
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top