March 27, 2009 at 12:45 pm
I have a table that has xml data:
datatype = ntext
Actual data is in XML format. The query built around this table joins at least 5 other tables. I just want one element of the XML, sample:
.
.
.
I want the value of whatIwant element. Can I extract this via SQL or I need to do the long way: process the XML in the application. This is as ASP.NET 2.0 web apps. I want the data into a data grid. I am looking for efficiency.
March 27, 2009 at 12:47 pm
You can cast ntext to nvarchar(max) and then cast that to XML, and then use XQuery on that.
XML disappears from the forums. If you want help on the actual query, the best bet is to save it in a txt file and upload that to the forum.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 12:47 pm
Hi
Two questions:
* Which version of SQL Server do you use?
* Could you please attach the XML as file or mask it? 😀
Greets
Flo
March 27, 2009 at 12:49 pm
Dang! 5 Seconds! 😉
March 27, 2009 at 12:58 pm
Did not know that XML code vanished!.
Attached sample data.
March 27, 2009 at 1:39 pm
Hi
Try this:
DECLARE @T TABLE (id int, txt NTEXT)
INSERT INTO @t
SELECT 1, '<root>
<response>
<whatIwant test="bla">value</whatIwant>
<entry1>some value </entry1>
<entry2>some value </entry2>
<entry3>some value </entry3>
</response>
</root>'
;WITH t_xml (id, txt) AS
(
SELECT id, CONVERT(XML, CONVERT(NVARCHAR(MAX), txt))
FROM @t
)
SELECT id,
T.C.value('./text()[1]', 'nvarchar(50)') data
--T2.C.query('.')
FROM t_xml
CROSS APPLY t_xml.txt.nodes('//root/response/whatIwant') T(C)
Greets
Flo
March 30, 2009 at 7:37 am
I cannot have a separate table for this as I am using this piece of info as part of several table joins.
March 30, 2009 at 11:07 am
Hi
I don't understand completely. You wrote that you have the XML within a NTEXT. So it must be within a table. It's not possible to declare an NTEXT variable.
If your XML is stored within one specific cell in a table select the data as first into an XML variable, then you can use it in your JOINS.
Greets
Flo
March 30, 2009 at 12:05 pm
My SQL is:
select col1, col2 ..... colNtextThatIsXML
From table1
Inner Join table 2 ...
Inner Join table 3 ...
Inner Join table 4 ...
From the colNtextThatIsXML, I just need one element ???
I did the type Cast as suggested:
cast(colThatIsXML as xml).value('(/root/response/ElementThatIWant)[1]','nvarchar(2000)') As 'ThisIsWhatIWant'
Works fine in SQL Query Manager, but my application hiccups with the following message:
System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
I am still stuck!
March 30, 2009 at 12:25 pm
Hi
So it's no SQL problem but a problem of your client connection. Set following option before executing:
SET ARITHABORT ON
Found it here:http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a874b4b2-05ad-4066-9811-30939188d695/
Greets
Flo
March 31, 2009 at 6:55 am
That works!.
I am debating on overheads this query will put on: Our production is hosted and cannot run the query analyzer here.
March 31, 2009 at 11:28 am
At the first place why dont you store this in XML type?? Btw which version of SQL Server you are using?
Thanks -- Vijaya Kadiyala
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply