SQL SERVER XML Query help

Hello Experts,
I am using SQL Server 2000 database. In one of my table field the data is stored a XML string. It is TEXT field.

Here is my SQL Query:
-----------------------------------
Select InterviewXML from InterviewTransactions Where InterviewId= 1601

Here is my Output:
-----------------------------
<Interview>
<Questions>
<Question ID="1800">
<Responses  Index="0">
<Response ID="idchkGeneralPartner" Index="0">General Partner</Response>
</Responses>
</Question>
<Question ID="1801">
<Responses  Index="0">
<Response ID="idName" Index="0">Silver</Response>
</Responses>
</Question>
<Question ID="1803">
<Responses  Index="0">
<Response ID="idName" Index="0">Bronze</Response>
</Responses>
</Question>
<Question ID="1806">
<Responses  Index="0">
<Response ID="idPercentOwnership" Index="0">100</Response>
</Responses>
</Question>
<Question ID="1807">
<Responses  Index="0">
<Response ID="idEffectiveDateOwnership" Index="0">02/17/2006</Response>
</Responses>
</Question>
<Question ID="1808">
<Responses  Index="0">
<Response ID="idSSN" Index="0">000353535</Response>
</Responses>
</Question>
<Question ID="1809">
<Responses  Index="0">
<Response ID="idAddressCountry" Index="183">UNITED STATES OF AMERICA</Response>
</Responses>
</Question>
<Question ID="1810">
<Responses  Index="0">
<Response ID="idStreetAddress" Index="0">address1</Response>
</Responses>
</Question>
<Question ID="1811">
<Responses  Index="0">
<Response ID="idStreetAddress2" Index="0">address2</Response>
</Responses>
</Question>
<Question ID="1812">
<Responses  Index="0">
<Response ID="idCity" Index="0">harrisburg</Response>
</Responses>
</Question>
<Question ID="1813">
<Responses  Index="0">
<Response ID="idAddressState" Index="38">Pennsylvania</Response>
</Responses>
</Question>
<Question ID="1819">
<Responses  Index="0">
<Response ID="idAddressCounty" Index="22">Cumberland</Response>
</Responses>
</Question>
<Question ID="1814">
<Responses  Index="0">
<Response ID="idZip" Index="0">17090</Response>
</Responses>
</Question>
<Question ID="1820">
<Responses  Index="0">
<Response ID="IdPhoneNo" Index="0">5533533535</Response>
</Responses>
</Question>
<Question ID="1821">
<Responses  Index="0">
<Response ID="IdPhoneNo" Index="0">35353</Response>
</Responses>
</Question>
<Question ID="1823">
<Responses  Index="0">
<Response ID="idEmail" Index="0">ddd@ggg.com</Response>
</Responses>
</Question>
</Questions>
</Interview>

---------------------------

I want to write another SQL query to pull the information like Name, Address, Phone, Email etc  from the XML field based on passing SSN in the where class.

Can someone help me out with this SQL query?

Thanks


LVL 8
mani_saiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
So you want to pass in the SSN, have it find that SSN in the XML and then return Name, Address, Phone, etc... from the XML?
0
mani_saiAuthor Commented:
Yes. That is what i want.
0
BrandonGalderisiCommented:
Do you have a full text index on that column?  Because trying to parse XML will be terribly inefficient.

If you can't add ssn to your table, you would want to use the full text index and search for.


'<Response ID="idSSN" Index="0">' + @SSN + '</Response>'

That will get you the correct XML record.  Then you just need to parse the XML using openxml.  Does any of your XML go over 4000 characters (if it's NTEXT) or 8000 characters (if TEXT).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mani_saiAuthor Commented:
Also the QuestionId for each question will never change.

For e.g: SSN will always have QuestionId 1808
0
mani_saiAuthor Commented:
The SQL query is for one time use only.  we are trying to get some information for bunch of records for the client.

So performance is not a issue.
0
Anthony PerkinsCommented:
You can do the following:
1. Retrieve the Xml value as an output parameter or in a resultset and
   2. Use your front-end app (VB.NET, C# or whatever) to retrieve the value
Or
   2. Resend as a text column.
   3. Use OPENXML to retrieve the value required.
0
mani_saiAuthor Commented:
The field data type is set as TEXT. Some XML records are more than 8000 characters.
0
mani_saiAuthor Commented:
acperkins,
Can you provide me sample query with your suggestion 2?

Thanks
0
chapmandewCommented:
declare @x xml
set @x = '<Interview>
<Questions>
<Question ID="1800">
<Responses  Index="0">
<Response ID="idchkGeneralPartner" Index="0">General Partner</Response>
</Responses>
</Question>
<Question ID="1801">
<Responses  Index="0">
<Response ID="idName" Index="0">Silver</Response>
</Responses>
</Question>
<Question ID="1803">
<Responses  Index="0">
<Response ID="idName" Index="0">Bronze</Response>
</Responses>
</Question>
<Question ID="1806">
<Responses  Index="0">
<Response ID="idPercentOwnership" Index="0">100</Response>
</Responses>
</Question>
<Question ID="1807">
<Responses  Index="0">
<Response ID="idEffectiveDateOwnership" Index="0">02/17/2006</Response>
</Responses>
</Question>
<Question ID="1808">
<Responses  Index="0">
<Response ID="idSSN" Index="0">000353535</Response>
</Responses>
</Question>
<Question ID="1809">
<Responses  Index="0">
<Response ID="idAddressCountry" Index="183">UNITED STATES OF AMERICA</Response>
</Responses>
</Question>
<Question ID="1810">
<Responses  Index="0">
<Response ID="idStreetAddress" Index="0">address1</Response>
</Responses>
</Question>
<Question ID="1811">
<Responses  Index="0">
<Response ID="idStreetAddress2" Index="0">address2</Response>
</Responses>
</Question>
<Question ID="1812">
<Responses  Index="0">
<Response ID="idCity" Index="0">harrisburg</Response>
</Responses>
</Question>
<Question ID="1813">
<Responses  Index="0">
<Response ID="idAddressState" Index="38">Pennsylvania</Response>
</Responses>
</Question>
<Question ID="1819">
<Responses  Index="0">
<Response ID="idAddressCounty" Index="22">Cumberland</Response>
</Responses>
</Question>
<Question ID="1814">
<Responses  Index="0">
<Response ID="idZip" Index="0">17090</Response>
</Responses>
</Question>
<Question ID="1820">
<Responses  Index="0">
<Response ID="IdPhoneNo" Index="0">5533533535</Response>
</Responses>
</Question>
<Question ID="1821">
<Responses  Index="0">
<Response ID="IdPhoneNo" Index="0">35353</Response>
</Responses>
</Question>
<Question ID="1823">
<Responses  Index="0">
<Response ID="idEmail" Index="0">ddd@ggg.com</Response>
</Responses>
</Question>
</Questions>
</Interview>'


select * from (
select FieldName = t.c.value('@ID','varchar(10)'), FieldValue = t.c.value('.','varchar(10)')
from @x.nodes('/Interview/Questions/Question/Responses/Response') t(c)
) a
where fieldname = 'idSSN' AND FieldValue = '000353535'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mani_saiAuthor Commented:
chapmandew:
Error on the sql statemnt:

Server: Msg 170, Level 15, State 1, Line 92
Line 92: Incorrect syntax near '.'.
0
chapmandewCommented:
it works fine for me...are you using 2005?
0
mani_saiAuthor Commented:
sql 2000
0
mani_saiAuthor Commented:
It is throwing error in this line:

From @x.nodes('/Interview/Questions/Question/Responses/Response') t(c)
0
mani_saiAuthor Commented:
for some reason @x.nodes is throwing the error.

Server: Msg 170, Level 15, State 1, Line 92
Line 92: Incorrect syntax near '.'.
0
mani_saiAuthor Commented:
can anyone suggest some other way of doing?  thanks.
0
Anthony PerkinsCommented:
Again, this is is not a simple query you have a choice, all involve using a front-end app using VB.NET, C# or VBScript, or whatever.  So here it is again:

1. Retrieve the Xml value as an output parameter or in a resultset and
   2. Use your front-end app (VB.NET, C# or whatever) to retrieve the value
Or
   2. Resend as a text column.
   3. Use OPENXML to retrieve the value required.

Pick your poison.
0
mani_saiAuthor Commented:
Can you explain more on this with code sample if you have:
 Resend as a text column./ Use OPENXML to retrieve the value required.

Thanks for your suggestions.

0
Anthony PerkinsCommented:
What is your front-end language?
0
mani_saiAuthor Commented:
There is no frontend involved. Only SQL Server 2000.

I need to Query a large XML data stored as text field and get some information. I found out a solution.

Thanks for your followup.
0
Anthony PerkinsCommented:
>>I found out a solution.<<
Can you post your solution, so that we can all learn how to do this in SQL Server 2000 (as Tim has pointed out it is trivial when using SQL Server 2005)

0
mani_saiAuthor Commented:
Here is the solution that worked for me, Drawback the solution as 8000 char SQL limitation.
----------------------------------------------------------


Declare @x table
(InterviewId int,
ssnvalue varchar(500),
ssnstart  int,
ssnend int,
legalnamestart int,
legalnameend int,
firstnamestart int,
firstnameend int,
lastnamestart int,
lastnameend int,
addressline1start int,
addressline1end int,
addressline2start int,
addressline2end int,
citystart int,
cityend int,
statestart int,
stateend int,
zipstart int,
zipend int,
countrystart int,
countryend int,
phonestart int,
phoneend int,
emailstart int,
emailend int,
xmlvalue text
)
 Insert into @x
Select
InterviewId,
'',  --ssnvalue
charindex('Question ID="1808"',Interviewxml) + 71,  --ssnstart
charindex('</Response>',Interviewxml,charindex('Question ID="1808"',Interviewxml)),  --ssnend
charindex('Response ID="idLegalName" Index="0"',Interviewxml) + 36, --legalnamestart
charindex('</Response>',Interviewxml,charindex('Response ID="idLegalName" Index="0"',Interviewxml)), --legalnameend
charindex('Question ID="1801"',Interviewxml) + 72, --FirstNamestart
charindex('</Response>',Interviewxml,charindex('Question ID="1801"',Interviewxml)), --FirstNameend
charindex('Question ID="1803"',Interviewxml) + 72, --LastNamestart
charindex('</Response>',Interviewxml,charindex('Question ID="1803"',Interviewxml)), --LastNameend
charindex('Question ID="1810"',Interviewxml) + 81, --AddressLine1start
charindex('</Response>',Interviewxml,charindex('Question ID="1810"',Interviewxml)), --AddressLine1end
charindex('Question ID="1811"',Interviewxml) + 82, --AddressLine2start
charindex('</Response>',Interviewxml,charindex('Question ID="1811"',Interviewxml)), --AddressLine2end
charindex('Question ID="1812"',Interviewxml) + 72, --citystart
charindex('</Response>',Interviewxml,charindex('Question ID="1812"',Interviewxml)), --cityend
charindex('Question ID="1813"',Interviewxml) + 81, --statestart
charindex('</Response>',Interviewxml,charindex('Question ID="1813"',Interviewxml)), --stateend
charindex('Question ID="1814"',Interviewxml) + 71, --zipstart
charindex('</Response>',Interviewxml,charindex('Question ID="1814"',Interviewxml)), --zipend
charindex('Question ID="1809"',Interviewxml) +84, --countrystart
charindex('</Response>',Interviewxml,charindex('Question ID="1809"',Interviewxml)), --countryend
charindex('Question ID="1820"',Interviewxml) +75, --phonestart
charindex('</Response>',Interviewxml,charindex('Question ID="1820"',Interviewxml)), --phoneend
charindex('Question ID="1823"',Interviewxml) +73, --emailstart
charindex('</Response>',Interviewxml,charindex('Question ID="1823"',Interviewxml)), --emailend
Interviewxml  --xmlvalue
from InterviewTransactions where Interviewxml like '%Response ID="idSSN%'


 
Update @x Set ssnvalue = substring(xmlvalue,ssnstart,ssnend-ssnstart) from @x

Select substring(xmlvalue,ssnstart,ssnend-ssnstart) as 'SSN',
substring(xmlvalue,legalnamestart,legalnameend-legalnamestart) as 'Legal Name' ,
substring(xmlvalue,firstnamestart,firstnameend-firstnamestart) as 'First Name' ,
substring(xmlvalue,lastnamestart,lastnameend-lastnamestart) as 'Last Name' ,
substring(xmlvalue,addressline1start,addressline1end-addressline1start) as 'Address Line1'  ,
substring(xmlvalue,addressline2start,addressline2end-addressline2start) as 'Address Line2' ,
substring(xmlvalue,citystart,cityend-citystart) as 'City'  ,
substring(xmlvalue,statestart,stateend-statestart) as 'State' ,
substring(xmlvalue,zipstart,zipend-zipstart) as 'Zipcode' ,
substring(xmlvalue,countrystart,countryend-countrystart) as 'Country'  ,
substring(xmlvalue,phonestart,phoneend-phonestart) as 'Phone'  ,
substring(xmlvalue,emailstart,emailend-emailstart) as 'Email'  from @x
where ssnvalue in ('555555555','444444444')
0
Anthony PerkinsCommented:
I am afraid there is only one way to describe that "solution": Ugly.
0
mani_saiAuthor Commented:
I know this is not a good solution .i would be interesed if any good solution that can get my results not using any front end. Just with SQL 2000.
0
Anthony PerkinsCommented:
I am afraid not.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.