stuff in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stuff in sql

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-24 : 23:58:31

how can i only grab only the ID 65489464678974667?
<HELLO a="65489464678974667"/><AMT a="2546.00"/>

sometimes the ID leght might not be same, example:
<HELLO a="ABC999"/><AMT a="3243.00"/>
<HELLO a="3874KIT"/><AMT a="5654.00"/>

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 00:28:28

DECLARE @XML XML ='<HELLO a="ABC999"/><AMT a="3243.00"/><HELLO a="3874KIT"/><AMT a="5654.00"/><HELLO a="65489464678974667"/><AMT a="2546.00"/>'
SELECT M.n.value('(@a)[1]' , 'VARCHAR(50)')
FROM @XML.nodes('/HELLO') M(n)

--
Chandu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 00:47:28

There are alot of data...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 01:00:19

Cross Apply it


with
cte as
(
	select	xmlcol = convert(xml, thecol)
	from	yourtbl
)
select	M.n.value('(@a)[1]' , 'VARCHAR(50)')
from	cte t
	cross apply xmlcol.nodes('/HELLO') as M(n)

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 02:33:52

i get this error.
what it meant ya?

XML parsing: line 1, character 73, illegal qualified name character

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 02:34:47

quote:
Originally posted by peace

i get this error.
what it meant ya?

XML parsing: line 1, character 73, illegal qualified name character

sorry, missed that with

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 02:43:01

Yup, i added in the 'WITH'

i get that error. is the converting issue?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 02:55:44

your data probably contains characters like &.

What is source of these data ? is it XML ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 03:01:40

is nvarchar..
i tried convert it to xml but getting that error.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 03:05:47

Refer is link
http://stackoverflow.com/questions/9920792/how-to-parse-xml-with-special-character-in-sql-server

--
Chandu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 04:05:26

how can i update to xml character as all data are different.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 04:14:38

alternatively use charindex(), substring() to parse the string and extract the required data

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 04:53:01

Can I apply the logic like if found 'HELLO a=' then grab the ID output..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 05:05:06

can you try this?


with
cte as
(
	select	xmlcol = convert(xml, '<Root>' + LTRIM(RTRIM(thecol)) + '</Root>')
	from	yourtbl
)
select	M.n.value('(@a)[1]' , 'VARCHAR(50)')
from	cte t
	cross apply xmlcol.nodes('/Root/HELLO') as M(n)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 05:11:28

Im getting this error:

XML parsing: line 1, character 79, illegal qualified name character

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 05:24:04

Ok then thats out of question

wat about this?


SELECT REPLACE(LEFT(theCol,CHARINDEX('"/>',thecol,CHARINDEX('<HELLO a="',thecol))-1),'<HELLO a="','') FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-25 : 05:39:42

im getting it now.
but can you explaint how it works?

some of the data appear like this:

<DESC v="gsgserg"/><AMT v="33.50"/><HELLO v="234231512351"/><Dcc::Applicable v="False"/>

where the HELLO v="... is in between.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 05:51:57

quote:
Originally posted by peace

im getting it now.
but can you explaint how it works?

some of the data appear like this:

<DESC v="gsgserg"/><AMT v="33.50"/><HELLO v="234231512351"/><Dcc::Applicable v="False"/>

where the HELLO v="... is in between.

you should have specified this in your first post itself as we cant guess how all values are coming in your case


SELECT  LEFT(theCol,CHARINDEX('"/>',thecol)-1)
FROM 
(SELECT STUFF(theCol,1,CHARINDEX('<HELLO a="',theCol)+ 10,'') AS theCol FROM Table)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources