SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / select from XML type ntext / Latest Posts

RE: select from XML type ntext

GSquared — Wed, 15 Jul 2009 13:04:11 GMT

The overhead will depends on how much data you're talking about.If the individual XML packages are small, it won't add much at all. If they're large, it'll add more.To put it in perspective, I've processed a 100 Meg XML file on my desktop machine, with an old Pentium D dual-core and 2 Gig of RAM, and it was plenty fast and didn't strain the system at all. If a slightly aged desktop machine can handle that, then it shouldn't be a strain for any reasonably well-built server.So, you'll have to judge it based on the size of the data being processed.

RE: select from XML type ntext

chandrika5 — Wed, 15 Jul 2009 12:06:23 GMT

Thanks - GSquared,I get the result. I'll work with this. I need to parse out a few 100s of such XMLs and will this add overheads to the servers.We are hosted and if any of our process is a overkill, they will remove the functionality. I need to read this only. There is no write.

RE: select from XML type ntext

GSquared — Wed, 15 Jul 2009 11:31:35 GMT

Make sure you copy from the "Quote" function, not "Copy and Paste", because the code windows on this site may be messing with the case of the words in the XML, and XML is case-sensitive.

RE: select from XML type ntext

GSquared — Wed, 15 Jul 2009 11:30:14 GMT

That's odd. I get this:[code]Node IDT varVal MyColumnSome Value1 d1 1 Some Value1Some Value2 d2 1 Some Value2some Value3 d3 1 some Value3some Value4 d4 1 some Value4Some Value5 d5 1 Some Value5My Local Path r1 5 My Local PathMy documents Path r2 5 My documents PathMy Programs Path r3 5 My Programs PathMy Pictures Path r4 5 My Pictures PathMy videos Path r5 5 My videos Path[/code]Are you sure you copied it correctly?

RE: select from XML type ntext

chandrika5 — Wed, 15 Jul 2009 11:21:40 GMT

Hi,I just ran the SQL and do not get anything!

RE: select from XML type ntext

GSquared — Wed, 15 Jul 2009 10:31:52 GMT

Take a look at this, see if you can work with it:[code]declare @String varchar(max), @XML XML;select @String = 'Some Value1Some Value2some Value3some Value4Some Value5My Local PathMy documents PathMy Programs PathMy Pictures PathMy videos Path'select @XML = @String;select Node, Node.value('(/simpleTable/@idt)[1]','char(2)') as IDT, Node.value('(/simpleTable/@varVal)[1]','varchar(100)') as varVal, Node.value('(/simpleTable)[1]','varchar(100)') as MyColumnfrom (select X.Y.query('.') as Node from @XML.nodes('Seq/MyElement/InnerElement/simpleTable') X(Y)) Z;[/code]The result I get from this looks like it could easily be concatenated into what you want.Does that help?

select from XML type ntext

chandrika5 — Wed, 15 Jul 2009 10:17:01 GMT

My sample XML is: Some Value1 Some Value2 some Value3 some Value4 Some Value5 My Local Path My documents Path My Programs Path My Pictures Path My videos Path The SQL:select cast(xmlString as xml).value('(/Seq/MyElement/InnerElement)[1]','nvarchar(3000)') AS 'One',cast(xmlString as xml).value('(/Seq/MyElement/InnerElement)[2]','nvarchar(3000)') AS 'Two'from myTableGives Me:One TwoSome Value1Some Value2SomeValue3 ... My Local PathMy documents PathMy Programs PathI need to stick in a delimiter between:Some Value1|some Value 2| .... My Local Path|My documents Path|My Programs PathIs it possible via SQL