October 25, 2011 at 11:48 am
To all, I have not worked with XML much at all so in this area I am a rookie.
Version SQL 2000, service packs are up to date and all patches are up to date.
I have a table of several thousand rows and it has several columns but one of the columns has xml data in it.
Here is an example of just one row for the xml data column.
<Questions> <Question value="Are you an IT Professional?" name="QQ1904_0" types="Radio"> <Answer value="No" /> </Question> <Question value="If Yes, please enter IT Number - if No, please enter None or N/A" name="QQ1905_1" types="Textbox"> <Answer value="N/A" /> </Question> <Question value="List States where IT Person has worked (if none, enter "N/A")" name="QQ1906_2" types="Textbox"> <Answer value="N/A" /> </Question> </Questions>
What I need to do is parse the xml data column and pull out the Answer value for the first question which is "Are you an IT Professional" . I need to do this for each row.
This is for a report I am writing in Reporting Services, and I am returning to the report other columns from other tables in the same query.
I am stuck on this item and I would appreciate any help that is offered.
Thanks in advance
Gary
November 4, 2011 at 6:09 am
Hi,
I'm not familiar with the XML syntax in SQL Server 2000, but I found this page that contained some information: http://msdn.microsoft.com/en-us/library/aa276847(v=SQL.80).aspx.
I tried a little and came to this (not finished) test:
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<Questions>
<Question value="Are you an IT Professional?" name="QQ1904_0" types="Radio">
<Answer value="No" />
</Question>
<Question value="If Yes, please enter IT Number - if No, please enter None or N/A" name="QQ1905_1" types="Textbox">
<Answer value="N/A" />
</Question>
<Question value="List States where IT Person has worked (if none, enter N/A)" name="QQ1906_2" types="Textbox">
<Answer value="N/A" />
</Question>
</Questions>' -- NOTE: I had to remove the double qoutes in the string of the last question!
SELECT *
FROM OPENXML(@hDoc, N'/Questions/Question')
SELECT * FROM OPENXML(@hDoc, N'/Questions/Question[@value="Are you an IT Professional?"]/Answer/@value')
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc
I hope that can help somewhat...
My other suggestion, would be to parse the xml ourselves. Since I prefer to nest statements when searching for strings in substrings, I would solve it something like this:
create table #test (
id int IDENTITY(1,1),
xmldata varchar(4000)
)
insert into #test (xmldata) values ('<Questions> <Question value="Are you an IT Professional?" name="QQ1904_0" types="Radio"> <Answer value="No" /> </Question> <Question value="If Yes, please enter IT Number - if No, please enter None or N/A" name="QQ1905_1" types="Textbox"> <Answer value="N/A" /> </Question> <Question value="List States where IT Person has worked (if none, enter "N/A")" name="QQ1906_2" types="Textbox"> <Answer value="N/A" /> </Question> </Questions>')
select SUBSTRING(Q1Answer, CHARINDEX('="', Q1Answer)+2, CHARINDEX('" />', Q1Answer)-CHARINDEX('="', Q1Answer)-2), *
from (select SUBSTRING(Q1start, CHARINDEX('<Answer', Q1start), 22) as Q1Answer, *
from (select SUBSTRING(xmldata, CHARINDEX('<Question value="Are you an IT Professional?"', xmldata), LEN(xmldata)) as Q1start, *
from #test
) x
) y
drop table #test
I hope these tips can help you to find a solution!
/Markus
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply