SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select from XML type ntext


select from XML type ntext

Author
Message
chandrika5
chandrika5
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 366
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
GSquared
GSquared
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117387 Visits: 9730
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
chandrika5
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 366
Hi,
I just ran the SQL and do not get anything!
GSquared
GSquared
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117387 Visits: 9730
That's odd. I get this:
Node   IDT   varVal   MyColumn
Some Value1 d1 1 Some Value1
Some Value2 d2 1 Some Value2
some Value3 d3 1 some Value3
some Value4 d4 1 some Value4
Some Value5 d5 1 Some Value5
My Local Path r1 5 My Local Path
My documents Path r2 5 My documents Path
My Programs Path r3 5 My Programs Path
My Pictures Path r4 5 My Pictures Path
My videos Path r5 5 My 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
GSquared
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117387 Visits: 9730
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
chandrika5
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 366
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
GSquared
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117387 Visits: 9730
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
benemngaycuoi511
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 0
G
[code="vb"][code="vb"]


[/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?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search