?
Solved

SQL  SERVER XML Query help

Posted on 2008-11-04
24
Medium Priority
?
219 Views
Last Modified: 2010-03-19
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


0
Comment
Question by:mani_sai
  • 14
  • 6
  • 2
  • +1
24 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22879051
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
 
LVL 8

Author Comment

by:mani_sai
ID: 22879107
Yes. That is what i want.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22879166
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:mani_sai
ID: 22879189
Also the QuestionId for each question will never change.

For e.g: SSN will always have QuestionId 1808
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22879216
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22879219
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
 
LVL 8

Author Comment

by:mani_sai
ID: 22879244
The field data type is set as TEXT. Some XML records are more than 8000 characters.
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22879260
acperkins,
Can you provide me sample query with your suggestion 2?

Thanks
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 900 total points
ID: 22879271
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
 
LVL 8

Author Comment

by:mani_sai
ID: 22879384
chapmandew:
Error on the sql statemnt:

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

Expert Comment

by:chapmandew
ID: 22879843
it works fine for me...are you using 2005?
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22879915
sql 2000
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22880070
It is throwing error in this line:

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

Author Comment

by:mani_sai
ID: 22880779
for some reason @x.nodes is throwing the error.

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

Author Comment

by:mani_sai
ID: 22880847
can anyone suggest some other way of doing?  thanks.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 22882745
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
 
LVL 8

Author Comment

by:mani_sai
ID: 22885337
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22891385
What is your front-end language?
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22894778
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22901152
>>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
 
LVL 8

Author Comment

by:mani_sai
ID: 22904074
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22909737
I am afraid there is only one way to describe that "solution": Ugly.
0
 
LVL 8

Author Comment

by:mani_sai
ID: 22910428
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22910491
I am afraid not.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question