OK.. But this is quite long winded.
USE [SVA_FLOWS]
GO
/****** Object: Table [dbo].[RUG_CLOB] Script Date: 19/12/2012 10:24:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RUG_CLOB](
[INDEX1] [varchar](50) NULL,
[DATA] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [dbo].[RUG_CLOB]
([INDEX1]
,[DATA])
VALUES
('NULL',
'ZHV|FF0000998|D0003001|C|SSSS|X|FFFF|20121203101202||||TR01|007|2300000000019|008|D0002|009|20121201090000|A|010|01|AE|011|20121201|
012|01|A|9.0|||||012|02|A|9.0|||||012|03|A|9.0|||||012|04|A|9.0|||||012|05|A|9.0|||||012|06|A|9.0|||||012|07|A|9.0|||||012|08|A|9.0|||||
012|09|A|9.0|||||012|10|A|9.0|||||012|11|A|9.0|||||012|12|A|9.0|||||012|13|A|9.0|||||012|14|A|9.0|||||012|15|A|9.0|||||012|16|A|9.0|||||012|17|A|9.0|||||
012|18|A|9.0|||||012|19|A|9.0|||||012|20|A|9.0|||||012|21|A|9.0|||||012|22|A|9.0|||||012|23|A|9.0|||||012|24|A|9.0|||||012|25|A|9.0|||||012|26|A|9.0|||||
012|27|A|9.0|||||012|28|A|9.0|||||012|29|A|9.0|||||012|30|A|9.0|||||012|31|A|9.0|||||012|32|A|9.0|||||012|33|A|9.0|||||012|34|A|9.0|||||012|35|A|9.0|||||
012|36|A|9.0|||||012|37|A|9.0|||||012|38|A|9.0|||||012|39|A|9.0|||||012|40|A|9.0|||||012|41|A|9.0|||||012|42|A|9.0|||||012|43|A|9.0|||||012|44|A|9.0|||||
012|45|A|9.0|||||012|46|A|9.0|||||012|47|A|9.0|||||012|48|A|9.0|||||013|01||ZPT|FF0000998|54||1|20121203101202|')
GO
My query currently is...
USE SVA_FLOWS
IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
CREATE TABLE #SPLITOUTDATA (
INDEX1 INT,
ROWNUM INT,
BITOFDATA VARCHAR(max));
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.Split_XML
(
@PARAMETER VARCHAR(MAX)
,@DELIMITER VARCHAR(1)
)
RETURNS @RESULT TABLE
(
ITEMNUMBER INT
,ITEMVALUE VARCHAR(MAX)
)
AS
BEGIN
DECLARE @XML XML ;
SET @PARAMETER = ( SELECT @PARAMETER
FOR XML PATH('')
) ;
SELECT @XML = '<r>' + REPLACE(@PARAMETER, @DELIMITER, '</r><r>') + '</r>' ;
INSERT INTO @RESULT
(
ITEMNUMBER,
ITEMVALUE
)
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) AS ITEMNUMBER
, Item.value('text()[1]', 'VARCHAR(MAX)') AS ITEMVALUE
FROM @XML.nodes('//r') R ( Item ) ;
RETURN ;
END ;
GO
;WITH
REFORMATTEDDATA AS
(
SELECT
ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS INDEX1,
REPLACE(REPLACE(CAST(DATA AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2
FROM
RUG_CLOB
WHERE
CAST(DATA AS VARCHAR(MAX)) LIKE 'ZHV|%|D0003001%'
)
INSERT INTO #SPLITOUTDATA
SELECT
INDEX1,
ROW_NUMBER()OVER(PARTITION BY INDEX1 ORDER BY split.ITEMNUMBER) AS ROWNUM,
split.ITEMVALUE AS BITOFDATA
FROM REFORMATTEDDATA
CROSS APPLY dbo.Split_XML(REFORMATTEDDATA.RAWCLOB2,'|') SPLIT
CREATE CLUSTERED INDEX idx1 ON #SPLITOUTDATA (INDEX1,ROWNUM)
INSERT INTO test.dbo.d0003
SELECT DISTINCT
(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 18) AS [Date&TimeOfReading],
(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 14) AS [MPAN],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 28) as [P1],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 36) as [P2],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 44) as [P3],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 52) as [P4],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 60) as [P5],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 68) as [P6],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 76) as [P7],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 84) as [P8],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 92) as [P9],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 100) as [P10],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 108) as [P11],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 116) as [P12],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 124) as [P13],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 132) as [P14],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 140) as [P15],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 148) as [P16],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 156) as [P17],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 164) as [P18],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 172) as [P19],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 180) as [P20],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 188) as [P21],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 196) as [P22],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 204) as [P23],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 212) as [P24],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 220) as [P25],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 228) as [P26],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 236) as [P27],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 244) as [P28],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 252) as [P29],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 260) as [P30],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 268) as [P31],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 276) as [P32],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 284) as [P33],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 292) as [P34],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 300) as [P35],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 308) as [P36],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 316) as [P37],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 324) as [P38],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 332) as [P39],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 340) as [P40],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 348) as [P41],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 356) as [P42],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 364) as [P43],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 372) as [P44],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 380) as [P45],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 388) as [P46],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 396) as [P47],
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 404) as [P48]
--(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 412) as [P49],
--(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 420) as [P50]
FROM #SPLITOUTDATA so1
--WHERE CLAUSE?
IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
People have pointed out that this code is not great, doesn't work well, but I work for a very small company and I'm just trying to do my best to get the data out that my bosses want for reports. I have quite little knowledge of SQL.