split function help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

split function help

Author

Topic

blummy99
Starting Member

5 Posts

Posted - 2011-10-26 : 17:22:32

I am trying to select from a table with a long text field that contains:

<?xml version="1.0"?><packet id="4ffc9067" requestId=" " type="request" deviceId="140003" time="2011-09-23T09:49:15" version="1.0"><presentation compression="none" encryption="none"><set><Data><Fifo><Record id="26314" locl="2011-09-23T05:48:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="129" emid="901815" ptyp="7" gate="2"></Record><Record id="26315" locl="2011-09-23T05:49:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="130" emid="900310" ptyp="7" gate="2"></Record></Fifo></Data></set></presentation></packet>

If I run the following select statement, I it only returns one row:

26314 140003 2011-09-23 05:48:00 901815

I need to return a row for each record id like this:

26314 140003 2011-09-23 05:48:00 901815
26315 140003 2011-09-23 05:49:00 900310

Here is the sql statement that I used:

SELECT
SUBSTRING(PAYLOAD,charindex('Record id',PAYLOAD)+11,charindex('"',PAYLOAD,charindex('Record id',PAYLOAD)+11)-charindex('Record id',PAYLOAD)-11) AS RECORDID,
SUBSTRING(PAYLOAD,charindex('deviceId',PAYLOAD)+10,charindex('"',PAYLOAD,charindex('deviceId',PAYLOAD)+10)-charindex('deviceId',PAYLOAD)-10) AS DEVICEID,
SUBSTRING(PAYLOAD,charindex('locl',PAYLOAD)+6,charindex('"',PAYLOAD,charindex('locl',PAYLOAD)+6)-charindex('locl',PAYLOAD)-15) AS TRANSACTIONDATE,
SUBSTRING(PAYLOAD,charindex('locl',PAYLOAD)+17,charindex('"',PAYLOAD,charindex('locl',PAYLOAD)+6)-charindex('locl',PAYLOAD)-17) AS TRANSACTIONTIME,
SUBSTRING(PAYLOAD,charindex('emid',PAYLOAD)+6,charindex('"',PAYLOAD,charindex('emid',PAYLOAD)+6)-charindex('emid',PAYLOAD)-6) AS PERSONNUM
FROM WIMCSTMWDMDEVFILES

For example, the text from the field payload from my temp table is shown above. This is the text in the field from one row in that table. I need two rows returned, one for each record id. THis row just happends to have two record ids. There could be rows in my temp table that have more than two record ids. I know that I need to create a function. I'm just not sure how.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 03:41:27

are you using SQL 2000? if its SQL 2000 you can use OPENXML to get data from within xml nodes directly rather than using series of charindex as above

see below query


declare @t table
(
ID int IDENTITY(1,1),
textval text
)
insert @t (textval)
select '<?xml version="1.0"?><packet id="4ffc9067" requestId=" " type="request" deviceId="140003" time="2011-09-23T09:49:15" version="1.0"><presentation compression="none" encryption="none"><set><Data><Fifo><Record id="26314" locl="2011-09-23T05:48:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="129" emid="901815" ptyp="7" gate="2"></Record><Record id="26315" locl="2011-09-23T05:49:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="130" emid="900310" ptyp="7" gate="2"></Record></Fifo></Data></set></presentation></packet>' union all
select '<?xml version="1.0"?><packet id="3edf325" requestId=" " type="request" deviceId="123456" time="2010-11-16T11:06:12" version="1.0"><presentation compression="none" encryption="none"><set><Data><Fifo><Record id="21324" locl="2011-03-12T19:48:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="129" emid="12334454" ptyp="7" gate="2"></Record><Record id="56876" locl="2011-11-11T03:23:00" trxi="13" rtyp="Q" rkey="Q " dstf="1" fkey="130" emid="3464574" ptyp="7" gate="2"></Record></Fifo></Data></set></presentation></packet>'

declare @result table
(
id  int,
locl datetime,
deviceid int ,
emid	int
)


DECLARE @i varchar(2000),@idoc int,@n int
select TOP 1 @n=ID,@i= textval FROM  @t ORDER BY ID

WHILE @n IS NOT NULL
BEGIN
SELECT @i= textval FROM  @t WHERE ID = @n
EXEC sp_xml_preparedocument @idoc OUTPUT, @i

INSERT INTO @Result
SELECT    *
FROM       OPENXML (@idoc, '/packet/presentation/set/Data/Fifo/Record',2)
            WITH (id  int './@id',
                  locl datetime './@locl',
				deviceid int 	'//@deviceId',
				emid	int './@emid')

EXEC sp_xml_removedocument @idoc
select @n=MIN(ID) FROM  @t WHERE ID > @n 
END

select * from @Result


output
----------------------------------------------
id	locl	deviceid	emid
26314	2011-09-23 05:48:00.000	140003	901815
26315	2011-09-23 05:49:00.000	140003	900310
21324	2011-03-12 19:48:00.000	123456	12334454
56876	2011-11-11 03:23:00.000	123456	3464574

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 03:42:18

if in sql 2005 or above use .nodes function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 04:12:31

ie it will be like below in 2005 and above


select m.u.value('./@id[1]','int') as id,
m.u.value('./@locl[1]','datetime') as locl,
m.u.value('../../../../../@deviceId[1]','int') as deviceid,
m.u.value('./@emid[1]','int') as emid
from (select ID,cast(textval as xml) as textval from @t) t
cross apply t.textval.nodes('/packet/presentation/set/Data/Fifo/Record') m(u)


output
------------------------------------------------------------
id	locl	                deviceid	 emid
26314	2011-09-23 05:48:00.000	140003	         901815
26315	2011-09-23 05:49:00.000	140003	         900310
21324	2011-03-12 19:48:00.000	123456	         12334454
56876	2011-11-11 03:23:00.000	123456	         3464574

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blummy99
Starting Member

5 Posts

Posted - 2011-11-01 : 17:54:08

Wow! Thanks for all of the replies. I'm going to try your suggestions. It is SQL 2005.

blummy99
Starting Member

5 Posts

Posted - 2011-11-01 : 18:39:03

This works but I had to hard code the dates. My table has over 20,000 rows and it took forever to the point where I just canceled it. This is ok because I'm going to create variables and pass the dates entered by the user when the custom report is executed. I tested both methods. I will use your second method since this is for SQL 2008. See below. THANK YOU!!!

--

declare @t table
(
ID int IDENTITY(1,1),
textval text
)
insert @t (textval)
--select payload from WIMCSTMWDMDEVFILES where createdtm >='10/23/2011' and CREATEDTM <= '10/25/2011'
select payload from WIMCSTMWDMDEVFILES where createdtm between '10/23/2011' and '10/25/2011'

declare @result table
(
id int,
locl datetime,
deviceid int ,
emid int
)

select m.u.value('./@id[1]','int') as id,
m.u.value('./@locl[1]','datetime') as locl,
m.u.value('../../../../../@deviceId[1]','int') as deviceid,
m.u.value('./@emid[1]','int') as emid
from (select ID,cast(textval as xml) as textval from @t) t
cross apply t.textval.nodes('/packet/presentation/set/Data/Fifo/Record') m(u)

/*DECLARE @i varchar(2000),@idoc int,@n int
select TOP 1 @n=ID,@i= textval FROM @t ORDER BY ID

WHILE @n IS NOT NULL
BEGIN
SELECT @i= textval FROM @t WHERE ID = @n
EXEC sp_xml_preparedocument @idoc OUTPUT, @i

INSERT INTO @Result
SELECT *
FROM OPENXML (@idoc, '/packet/presentation/set/Data/Fifo/Record',2)
WITH (id int './@id',
locl datetime './@locl',
deviceid int '//@deviceId',
emid int './@emid')

EXEC sp_xml_removedocument @idoc
select @n=MIN(ID) FROM @t WHERE ID > @n
END*/

--select * from @Result order by 2 asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:51:01

if its sql 2005 better to use second suggestion (.nodes)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources