Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select from XML type ntext Expand / Collapse
Author
Message
Posted Wednesday, July 15, 2009 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 07, 2014 12:43 PM
Points: 142, Visits: 307
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




Post #753647
Posted Wednesday, July 15, 2009 10:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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?


- 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
Post #753659
Posted Wednesday, July 15, 2009 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 07, 2014 12:43 PM
Points: 142, Visits: 307
Hi,
I just ran the SQL and do not get anything!
Post #753684
Posted Wednesday, July 15, 2009 11:30 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
That's odd. I get this:
Node	IDT	varVal	MyColumn
<simpleTable idt="d1" varVal="1">Some Value1</simpleTable> d1 1 Some Value1
<simpleTable idt="d2" varVal="1">Some Value2</simpleTable> d2 1 Some Value2
<simpleTable idt="d3" varVal="1">some Value3</simpleTable> d3 1 some Value3
<simpleTable idt="d4" varVal="1">some Value4</simpleTable> d4 1 some Value4
<simpleTable idt="d5" varVal="1">Some Value5</simpleTable> d5 1 Some Value5
<simpleTable idt="r1" varVal="5">My Local Path</simpleTable> r1 5 My Local Path
<simpleTable idt="r2" varVal="5">My documents Path</simpleTable> r2 5 My documents Path
<simpleTable idt="r3" varVal="5">My Programs Path</simpleTable> r3 5 My Programs Path
<simpleTable idt="r4" varVal="5">My Pictures Path</simpleTable> r4 5 My Pictures Path
<simpleTable idt="r5" varVal="5">My videos Path</simpleTable> 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
Post #753687
Posted Wednesday, July 15, 2009 11:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #753690
Posted Wednesday, July 15, 2009 12:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 07, 2014 12:43 PM
Points: 142, Visits: 307
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.
Post #753706
Posted Wednesday, July 15, 2009 1:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #753742
Posted Sunday, September 08, 2013 4:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 08, 2013 4:46 PM
Points: 1, Visits: 0
G
[code="vb"][code="vb"][code="xml"]

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

Post #1492629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse