Siebel script export

From time to time, it would be very useful to conduct code analysis on the script that is buried within the multiple tables of the Siebel Repositories.
Things like:

• line count
• code complexity calculations (edge detection)
• release over release comparisons
  
• etc

Getting the code out of the repository is a pain. I can use the repository search functionality to find specific things, but its tough.
To solve this I fired up my sql editor and using the xml features of sql 2005, I built a query that exports the script into a big xml document.
One can then run xslt's or other scripts against the code file.
Below is the script:

DECLARE @APPLET_BROWSER XML
DECLARE @APPLET_SERVER XML
DECLARE @BUSINESS_COMP_BROWSER XML
DECLARE @BUSINESS_COMP_SERVER XML
DECLARE @BUSINESS_SERVICE_BROWSER XML
DECLARE @BUSINESS_SERVICE_SERVER XML
DECLARE @APPLICATION_BROWSER XML
DECLARE @APPLICATION_SERVER XML

SET @APPLET_SERVER =
(
SELECT
'Applet Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APLT_BRSSCRPT D
WHERE D.APPLET_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_APPLET A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APLT_BRSSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLET_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLET_BROWSER =
(
SELECT
'Applet Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_WEBSCRPT D
WHERE D.APPLET_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_APPLET A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_WEBSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLET_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_COMP_SERVER =
(
SELECT
'Business Component Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_BUSCOMP_SCRIPT D
WHERE D.BUSCOMP_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_BUSCOMP A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_BUSCOMP_SCRIPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.BUSCOMP_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_COMP_BROWSER =
(
SELECT
'Business Component Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_BC_BRS_SCRPT D
WHERE D.BUSCOMP_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_BUSCOMP A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_BC_BRS_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.BUSCOMP_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_SERVICE_SERVER =
(
SELECT
'Business Service Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_SERVICE_SCRPT D
WHERE D.SERVICE_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_SERVICE A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_SERVICE_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.SERVICE_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_SERVICE_BROWSER =
(
SELECT
'Business Service Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_SVC_BRS_SCRPT D
WHERE D.SERVICE_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_SERVICE A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_SVC_BRS_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.SERVICE_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLICATION_SERVER =
(
SELECT
'Application Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_SCRIPT D
WHERE D.APPLICATION_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_APPLICATION A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_SCRIPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLICATION_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLICATION_BROWSER =
(
SELECT
'Application Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_BRSSCRPT D
WHERE D.APPLICATION_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_APPLICATION A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_BRSSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLICATION_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)


SELECT @BUSINESS_COMP_SERVER.query('//Type')
UNION ALL
SELECT @BUSINESS_COMP_BROWSER.query('//Type')
UNION ALL
SELECT @BUSINESS_SERVICE_SERVER.query('//Type')
UNION ALL
SELECT @BUSINESS_SERVICE_BROWSER.query('//Type')
UNION ALL
SELECT @APPLICATION_SERVER.query('//Type')
UNION ALL
SELECT @APPLICATION_BROWSER.query('//Type')
UNION ALL
SELECT @APPLET_BROWSER.query('//Type')
UNION ALL
SELECT @APPLET_SERVER.query('//Type')
FOR XML PATH(''), ROOT('Scripting'), TYPE