select from XML type ntext

Question

Post reply

select from XML type ntext

chandrika5

SSCrazy

Points: 2734
More actions
July 15, 2009 at 10:17 am

#212443

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 myTable
Gives Me:
One Two
Some Value1Some Value2SomeValue3 ... My Local PathMy documents PathMy Programs Path
I need to stick in a delimiter between:
Some Value1|some Value 2| .... My Local Path|My documents Path|My Programs Path
Is it possible via SQL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at this, see if you can work with it:

declare @String varchar(max), @XML XML;

select @String =

'

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

'

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 MyColumn

from

(select X.Y.query('.') as Node

from @XML.nodes('Seq/MyElement/InnerElement/simpleTable') X(Y)) Z;

The result I get from this looks like it could easily be concatenated into what you want.

Does that help?

- 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

chandrika5 SSCrazy Points: 2734 More actions · Answer 2

chandrika5

SSCrazy

Points: 2734

July 15, 2009 at 11:21 am

#1025244

Hi,

I just ran the SQL and do not get anything!

GSquared SSC Guru Points: 260824 More actions · Answer 3

That's odd. I get this:

NodeIDTvarValMyColumn

Some Value1d11Some Value1

Some Value2d21Some Value2

some Value3d31some Value3

some Value4d41some Value4

Some Value5d51Some Value5

My Local Pathr15My Local Path

My documents Pathr25My documents Path

My Programs Pathr35My Programs Path

My Pictures Pathr45My Pictures Path

My videos Pathr55My videos Path

Are you sure you copied it correctly?

- 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

GSquared SSC Guru Points: 260824 More actions · Answer 4

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.

- 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

chandrika5 SSCrazy Points: 2734 More actions · Answer 5

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.

GSquared SSC Guru Points: 260824 More actions · Answer 6

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.

- 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

benemngaycuoi511 SSC Journeyman Points: 79 More actions · Answer 7

G[/code][/code][/code]Squared (7/15/2009)
Take a look at this, see if you can work with it:
declare @String varchar(max), @XML XML;
select @String =
'<Seq>
<MyElement Number="12">
<InnerElement>
<simpleTable idt="d1" varVal="1">Some Value1</simpleTable>
<simpleTable idt="d2" varVal="1">Some Value2</simpleTable>
<simpleTable idt="d3" varVal="1">some Value3</simpleTable>
<simpleTable idt="d4" varVal="1">some Value4</simpleTable>
<simpleTable idt="d5" varVal="1">Some Value5</simpleTable>
</InnerElement>
<InnerElement>
<simpleTable idt="r1" varVal="5">My Local Path</simpleTable>
<simpleTable idt="r2" varVal="5">My documents Path</simpleTable>
<simpleTable idt="r3" varVal="5">My Programs Path</simpleTable>
<simpleTable idt="r4" varVal="5">My Pictures Path</simpleTable>
<simpleTable idt="r5" varVal="5">My videos Path</simpleTable>
</InnerElement>
</MyElement>
</Seq>'
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 MyColumn
from
(select X.Y.query('.') as Node
from @XML.nodes('Seq/MyElement/InnerElement/simpleTable') X(Y)) Z;
The result I get from this looks like it could easily be concatenated into what you want.
Does that help?