Solved

Select statement to query a value from XML data in a SQL 2008 R2 ntext column

Posted on 2013-12-19
24
1,473 Views
Last Modified: 2013-12-26
With a Table called tblNaDevEventsUsersSignedUpToEvent and the XML below in a column called userSignUpAnswers, what SELECT Statement would return the 4 digit SSN?

That would be: return the value of <UserSelectedValue> WHERE <EventSignUpQuestionOptionID> = 121

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSignUpQuestionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1023</EventSignUpQuestionId>
    <QuestionName>Last 4 digits of your SS#: </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>121</EventSignUpQuestionOptionId>
      <UserSelectedValue>5962</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1016</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for at least 90 days?</QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>122</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1017</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for more than 160 days? </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>82</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
</ArrayOfSignUpQuestionInfo>

Open in new window


I don't know XML / XQuery at all and only basic T-SQL.  I appreciate the help!
(The XML above was formatted in Visual Studio, but in the SQL table it's just a long string.)
-David
0
Comment
Question by:megnin
  • 14
  • 6
  • 4
24 Comments
 
LVL 8

Expert Comment

by:vr6r
Comment Utility
SELECT 
  CAST(userSignUpAnswers AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM
  tblNaDevEventsUsersSignedUpToEvent
WHERE
  CAST(userSignUpAnswers AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/EventSignUpQuestionOptionID)[1]', 'INT') = 121

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Try it this way:
SELECT userSignUpAnswers.value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'varchar(9)')
FROM  tblNaDevEventsUsersSignedUpToEvent
WHERE userSignUpAnswers.exist('ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId="121"') = 1

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
This is how I tested it:
DECLARE @tblNaDevEventsUsersSignedUpToEvent TABLE (userSignUpAnswers xml)
INSERT @tblNaDevEventsUsersSignedUpToEvent (userSignUpAnswers)
VALUES (
'<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSignUpQuestionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1023</EventSignUpQuestionId>
    <QuestionName>Last 4 digits of your SS#: </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>121</EventSignUpQuestionOptionId>
      <UserSelectedValue>5962</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1016</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for at least 90 days?</QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>122</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1017</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for more than 160 days? </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>82</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
</ArrayOfSignUpQuestionInfo>')

SELECT userSignUpAnswers.value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'varchar(9)')
FROM  @tblNaDevEventsUsersSignedUpToEvent
WHERE userSignUpAnswers.exist('ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId="121"') = 1

Open in new window

Output:
5962
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Thanks I'll give those solutions a try when I get back to the office.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
With both solutions, I go an error, "unable to switch the encoding."

I try modifying those statements, but I'll be just trying stuff at random, I really have no experience working with XML in SQL.  If anyone knows what preventing it from working I'd be very grateful to learn it.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
I tried this and got the same error:
SELECT 
  CAST(cast(usersignupanswers as nvarchar(4000)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM
  tblNaDevEventsUsersSignedUpToEvent
WHERE
  CAST(cast(usersignupanswers as nvarchar(4000)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/EventSignUpQuestionOptionID)[1]', 'INT') = 121

Open in new window


Here is a query against the database, including the table I'm working with that does work.  It includes (right in the middle of the SELECT) the userSignUpAnswers column.  In the database the userSignUpAnswers column is of "ntext" datatype.  It contains the fully formed XML code in my first post.

This query works, but it only returns the full XML code in a single column. I need that one 4 digit value. (See my first post.)
use workforce1b_dnn;
declare @dt1 smalldatetime;
set @dt1='12/01/2013';

select e.eventname, e.eventsummary, e.eventstartdate,
s.bookingid, s.eventid, s.userid, s.billingaddressid, s.eventstartdate, s.eventenddate,
u.lastname as useracctlname, u.firstname as useracctfname,
a.surname as lastname, a.firstname,
cast(s.usersignupanswers as nvarchar(4000)) as signupanswers
from tblnadeveventsuserssigneduptoevent s, tblnadevevents e, tblnadeveventsuserssignedupaddress a, users u
where 
s.eventid=e.eventid
and s.userid=u.userid
and s.billingaddressid=a.addressid
and cast(s.eventstartdate as smalldatetime) >= @dt1

Open in new window

0
 
LVL 8

Expert Comment

by:vr6r
Comment Utility
That error comes from a mismatch between the xml encoding and the encoding of the column it's stored in within sql.  I'm guessing from that error that your table column is NVARCHAR, is that right?  That results in your non-unicode (utf-8) xml being stored in a unicode column.

At any rate, try this.  
(Note the extra cast to VARCHAR(MAX) prior to casting to XML to resolve the encoding error):

SELECT CAST(CAST(userSignUpAnswers AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(userSignUpAnswers AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window

0
 
LVL 1

Author Comment

by:megnin
Comment Utility
acperkins, I think the problem with the "unable to switch encoding" is that the datatype in the database is "ntext."  

Even in the query above that does work, if I change
cast(s.usersignupanswers as nvarchar(4000)) as signupanswers
to
cast(s.usersignupanswers as XML) as signupanswers
I get that encoding error.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
vr6r, yes, I think we're getting somewhere, but on that I got this message:
Msg 9438, Level 16, State 1, Line 43
XML parsing: line 1, character 7, text/xmldecl not at the beginning of input

I don't know what that means.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
vr6r, I even tried adding a third extra cast, but that made no difference at all:

SELECT CAST(CAST(CAST(userSignUpAnswers as nvarchar(4000)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(CAST(userSignUpAnswers as nvarchar(4000)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window

0
 
LVL 8

Expert Comment

by:vr6r
Comment Utility
Ah, sounds like there might be a space in front of the xml in that ntext column.

Try this one (another small change to LTRIM the xml text):
SELECT CAST(CAST(LTRIM(userSignUpAnswers) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(LTRIM(userSignUpAnswers) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window

0
 
LVL 1

Author Comment

by:megnin
Comment Utility
There is something in front of the <?xml version... because I just copied the contents of the column and pasted into the text editor "EditPad Pro" which allows for different text encoding types and it replaced some non-printing character with a question mark making it, ?<?xml version="1.0" encoding="utf-8"?>

It's not actually a question mark, EditPad just uses that to note some character that it doesn't understand in the present encoding type.

I tried that and got this, but I think your close:

Msg 8116, Level 16, State 1, Line 43
Argument data type ntext is invalid for argument 1 of ltrim function.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:megnin
Comment Utility
I changed the encoding in the text editor to Unicode utf-8 and the space in front of <?xml version="1.0" went away.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
The space went away just in the text editor.  The error is unchanged.
0
 
LVL 8

Expert Comment

by:vr6r
Comment Utility
Try one of these...  this first one moves the ltrim outside of the cast to varchar:

SELECT CAST(LTRIM(CAST(userSignUpAnswers AS VARCHAR(MAX))) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(LTRIM(CAST(userSignUpAnswers AS VARCHAR(MAX))) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window


If that still doesn't work (there is still some non-whitespace character in front), try this one.  It finds the correct starting point using "<?xml" and ignores anything that comes before it.  It should work, but as a long-term solution I would recommend trying to find what those extra characters are and stopping them from getting stored along with the xml.
SELECT CAST(CAST(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),LEN(userSignUpAnswers)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),LEN(userSignUpAnswers)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window

0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Yeah, I also tried moving the LTRIM to different places and got the same result in the places where it even worked.  I tried your first one in case you did something different than I had tried and got the same result.  I'm not sure what's going on in that column of the database.  We don't have any control on what or how is stored in the database because it's a third party plugin in our website that captures workshop sign-ups.

The second one gave me this message:
Msg 8116, Level 16, State 1, Line 48
Argument data type ntext is invalid for argument 1 of len function.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
If this does not turn out to be an easy solution, I think we are going to scrap that plug-in and look for a different workshop sign-up module that produces a better set of data.  It seems that "ntext" was a poor choice of datatype for a column containing only XML.
0
 
LVL 8

Expert Comment

by:vr6r
Comment Utility
Oh right ntext... this should work (len doesn't work with ntext, swapped it out for datalength):
SELECT CAST(CAST(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),DATALENGTH(userSignUpAnswers)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),DATALENGTH(userSignUpAnswers)) AS VARCHAR(MAX)) AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window


I agree though - if they are only storing xml I would question why they don't just use the xml type.  Or at the very least scrub the data to ensure no misc. characters are getting stored along with it.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Yeah, I don't know what's going on with the data.  That last one gave me this message:

Msg 9420, Level 16, State 1, Line 48
XML parsing: line 1, character 798, illegal xml character

But, character 798 falls right at the >< in the section below:

</Answers></SignUpQuestionInfo>

I don't see anything invalid about that.
0
 
LVL 8

Accepted Solution

by:
vr6r earned 500 total points
Comment Utility
Ugh so close.  If you're still interested in trying, give this one a shot.  Assuming that the character it's choking on in the last error you mentioned is a unicode char that our varchar doesn't understand, this might help...
SELECT CAST(REPLACE(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),DATALENGTH(userSignUpAnswers)), 'encoding="utf-8"', 'encoding="utf-16"') AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'VARCHAR(32)') AS UserSelectedValue
FROM tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(REPLACE(SUBSTRING(userSignUpAnswers,CHARINDEX('<?xml',userSignUpAnswers),DATALENGTH(userSignUpAnswers)), 'encoding="utf-8"', 'encoding="utf-16"') AS XML).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId)[1]', 'VARCHAR(32)') = 121

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I think we are going to scrap that plug-in and look for a different workshop sign-up module that produces a better set of data.  It seems that "ntext" was a poor choice of datatype for a column containing only XML.
I agree absolutely.  Any vendor who still insists on using text or ntext data types, should be quietly taken outside and shot.

Here is what is happening:
ntext cannot be used with encoding="utf-8"

If you want to use ntext then you need to change your Xml to utf-16,  See here:
DECLARE @tblNaDevEventsUsersSignedUpToEvent TABLE (userSignUpAnswers ntext)
INSERT @tblNaDevEventsUsersSignedUpToEvent (userSignUpAnswers)
VALUES (
'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfSignUpQuestionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1023</EventSignUpQuestionId>
    <QuestionName>Last 4 digits of your SS#: </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>121</EventSignUpQuestionOptionId>
      <UserSelectedValue>5962</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1016</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for at least 90 days?</QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>122</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1017</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for more than 160 days? </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>82</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
</ArrayOfSignUpQuestionInfo>')

SELECT CAST(userSignUpAnswers AS Xml).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'varchar(9)')
FROM  @tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(userSignUpAnswers AS Xml).exist('ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId="121"') = 1

Open in new window


If on the other hand you want to use utf-8 and you cannot use Xml and have to use one of those deprecated data types then use text (not ntext). See here:
DECLARE @tblNaDevEventsUsersSignedUpToEvent TABLE (userSignUpAnswers text)
INSERT @tblNaDevEventsUsersSignedUpToEvent (userSignUpAnswers)
VALUES (
'<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSignUpQuestionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1023</EventSignUpQuestionId>
    <QuestionName>Last 4 digits of your SS#: </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>121</EventSignUpQuestionOptionId>
      <UserSelectedValue>5962</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1016</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for at least 90 days?</QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>122</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1017</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for more than 160 days? </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>82</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
</ArrayOfSignUpQuestionInfo>')

SELECT CAST(userSignUpAnswers AS Xml).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'varchar(9)')
FROM  @tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(userSignUpAnswers AS Xml).exist('ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId="121"') = 1

Open in new window


If you still want to continue living in the dark ages as in ntext and utf-8, then you are going to have to do something rather ugly like this:
DECLARE @tblNaDevEventsUsersSignedUpToEvent TABLE (userSignUpAnswers ntext)
INSERT @tblNaDevEventsUsersSignedUpToEvent (userSignUpAnswers)
VALUES (
'<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSignUpQuestionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1023</EventSignUpQuestionId>
    <QuestionName>Last 4 digits of your SS#: </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>121</EventSignUpQuestionOptionId>
      <UserSelectedValue>5962</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1016</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for at least 90 days?</QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>122</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
  <SignUpQuestionInfo>
    <EventSignUpQuestionId>1017</EventSignUpQuestionId>
    <QuestionName>Have you been unemployed for more than 160 days? </QuestionName>
    <Answers>
      <EventSignUpQuestionOptionId>82</EventSignUpQuestionOptionId>
      <UserSelectedValue>Yes</UserSelectedValue>
      <PriceAdjustment>0.0000</PriceAdjustment>
    </Answers>
  </SignUpQuestionInfo>
</ArrayOfSignUpQuestionInfo>')

SELECT CAST(CAST(userSignUpAnswers AS text) as Xml).value('(ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/UserSelectedValue)[1]', 'varchar(9)')
FROM  @tblNaDevEventsUsersSignedUpToEvent
WHERE CAST(CAST(userSignUpAnswers AS text) AS Xml).exist('ArrayOfSignUpQuestionInfo/SignUpQuestionInfo/Answers/EventSignUpQuestionOptionId="121"') = 1

Open in new window


All of these produce the same output, namely "5962"

Good luck.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
There is something in front of the <?xml version... because I just copied the contents of the column and pasted into the text editor "EditPad Pro"
They are called BOM characters.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
vr6r,  That last one, my friend, worked.  :-D

From my actual data, it gave me a list of 4 digit SSNs, which is exactly what I'm looking for.

The people who gave the specs to the 3rd party who is doing our web site failed to give them any usable specs for the output of that Workshop Registration Module, so the output we go was a ridiculous table where the entire answer set was in one ntext field containing all the data in XML.  We were left to try and figure out how to use it anyway.  The rest of the team just thought it was garbage text.  I recognized it as XML, but my skills with it aren't that great. I really appreciate all the time to took to help me out.  :-)

acperkins,  the block of XML is what we are given as output from a module.  We have no control over it's content or data type.  I have to do something with it as-is, or not, but that's it.  I'll have to Google what a "BOM" character is.  I've never heard of that. (I don't think.)  I do appreciate your suggestions, though.
0
 
LVL 1

Author Closing Comment

by:megnin
Comment Utility
Genius!  Thank you so much.  I just copied and pasted your solution and it worked with no tweaking necessary.  Awesome!  Thank you so much.  :-D
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now