January 2, 2009 at 3:55 am
Hi,
DECLARE @xml XML
DECLARE @QuestionCount INT
DECLARE @Count INT
SET @Count = 1
SET @xml =
'
'
SET @QuestionCount = @xml.value('count(/QuestionList/Question)','int')
WHILE @Count <= @QuestionCount
BEGIN
INSERT INTO tblQuestion
SELECT Ques.value('TextData[1]','varchar(16)') AS Question
FROM @xml.nodes('//Question[sql:variable("@Count")][1]') AS PP(Ques)
INSERT INTO tblAnswer
SELECT SCOPE_IDENTITY(),
Ques.value('Option1[1]','varchar(16)') AS Option1,
Ques.value('Option2[1]','varchar(16)') AS Option2,
Ques.value('Option3[1]','varchar(16)') AS Option3
FROM @xml.nodes('//Question[sql:variable("@Count")][1]') AS PP(Ques)
SET @Count = @Count + 1
END
SELECT * FROM tblQuestion
SELECT * FROM tblAnswer
If i run the above query it will display as:
fldQuestionID fldQuestionText
------------- ----------------
17 What is Test
18 What is Dummy
(2 row(s) affected)
fldQuestionID fldOption1 fldOption2 fldOption3
------------- ---------------- ---------------- ----------------
17 Test1 Test2
18 Dummy1 Dummy2 Dummy3
In this fldOption3 column against fldQuestionID = 17, it shows as EMPTY.
Here i want to display some character rather than EMPTY. How can i handle this. Is it possible ISNULL function in the xml parser?
Help me to solve this.
---
January 2, 2009 at 8:20 am
I don't think you could use it in the XML, but you could do this in your insert:
SELECT
SCOPE_IDENTITY(),
Ques.value('Option1[1]','varchar(16)') AS Option1,
Ques.value('Option2[1]','varchar(16)') AS Option2,
Case
When Ques.value('Option3[1]','varchar(16)') = '' Then 'New value'
Else Ques.value('Option3[1]','varchar(16)')
End AS Option3
FROM
@xml.nodes('//Question[sql:variable("@Count")][1]') AS PP(Ques)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 5:35 am
Thanks Jack for your valuable reply.
Here i was using NULLIF function. Using this i got the result. Please see the below code.
SELECT SCOPE_IDENTITY(),
ISNULL(NULLIF(Ques.value('Option1[1]','varchar(16)'),''),NULL) AS Option1,
ISNULL(NULLIF(Ques.value('Option2[1]','varchar(16)'),''),NULL) AS Option2,
ISNULL(NULLIF(Ques.value('Option3[1]','varchar(16)'),''),NULL) AS Option3
FROM @xml.nodes('//Question[sql:variable("@Count")][1]') AS PP(Ques)
Please correct me if any thing in this goes wrong.:)
---
January 5, 2009 at 6:26 am
Sure the nested ISNULL(NULLIF()) works, I just find the Case statement easier to read. I'd run both in the same SSMS window and see which one is more efficient.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply