SQL  SERVER XML Query help

Mani Pazhana
Mani Pazhana used Ask the Experts™
on
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


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?

Author

Commented:
Yes. That is what i want.
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).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

For e.g: SSN will always have QuestionId 1808

Author

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.
Top Expert 2012

Commented:
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.

Author

Commented:
The field data type is set as TEXT. Some XML records are more than 8000 characters.

Author

Commented:
acperkins,
Can you provide me sample query with your suggestion 2?

Thanks
Awarded 2008
Awarded 2008
Commented:
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'

Author

Commented:
chapmandew:
Error on the sql statemnt:

Server: Msg 170, Level 15, State 1, Line 92
Line 92: Incorrect syntax near '.'.
Awarded 2008
Awarded 2008

Commented:
it works fine for me...are you using 2005?

Author

Commented:
sql 2000

Author

Commented:
It is throwing error in this line:

From @x.nodes('/Interview/Questions/Question/Responses/Response') t(c)

Author

Commented:
for some reason @x.nodes is throwing the error.

Server: Msg 170, Level 15, State 1, Line 92
Line 92: Incorrect syntax near '.'.

Author

Commented:
can anyone suggest some other way of doing?  thanks.
Top Expert 2012
Commented:
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.

Author

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.

Top Expert 2012

Commented:
What is your front-end language?

Author

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.
Top Expert 2012

Commented:
>>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)

Author

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')
Top Expert 2012

Commented:
I am afraid there is only one way to describe that "solution": Ugly.

Author

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.
Top Expert 2012

Commented:
I am afraid not.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial