Help With Transactional Data stored in XML CLOBs

Insurance/Reinsurance

The MS SQL Server 2005 database application I am supporting uses XML CLOBs to store data entered by the user. I believe the vendor thought this design was best for flexibility when many UI changes from customers (us) were expected during implementation.

The Issue
There are four tables used for storing very important transactional data as XML CLOBs. The structure on each is:
PK (int)
XML (text) * yes text not type XML *

The issue is that we want to extract the data stored as XML CLOBs and insert into normalized table/columns structures and we have not been successful. The transactional data stored in the XML CLOBs is defined as type <string></string> and the XML schema types refer only to UI field names.

What would be the best approach and what functions can I use to extract and translate up to 2GB worth of XML CLOBs per row per four tables?

Could you post an example of what you want to achieve with a sample of data stored in the XML(text) field?

Here's the XML Content schema

<java version="1.5.0_12" class="java.beans.XMLDecoder">
<object class="java.util.HashMap">
-------------------------------------------
<void method="put">
<string></string>
<string></string>
</void>
-------------------------------------------
</object>
</java>

The <void></void> node repeats 57 times for each XML CLOB row.

A sample of data in row position [1] & [2]:

<void method="put"><string>Product[0].Account Information[0].Similar Account Flag</string>
<string>false</string>

So I've made some excellent progress extracting both strings using XQuery.Value

Select
x_clob.value ('(/java/object/void/string) [1]', 'varchar(max)')
From
XML.X_CLOB

The first problem is incrementing the nodes. I've used while loops with defined @i variable without success and have used the brute force method of defining 114 select statements

x_clob.value ('(/java/object/void/string) [n]', 'varchar(max)')

where n = 1-114

This method, while definately not elegent gets me all of the data for every row in the table ... but it spans 114 nodes across the results.

NODE 1 value NODE 2 value ... NODE 114 value

The odd nodes are the application field names and the even nodes are the field values

I need the output to be in the form of

NODE 1 VALUE, NODE 3 VALUE ... NODE 113 VALUE
---------------------------------------------------------
NODE 2 VALUE, NODE 4 VALUE ... NODE 114 VALUE

Any help you might offer is greatly appreciated.

<java version="1.5.0_12" class="java.beans.XMLDecoder">
<object class="java.util.HashMap">
-------------------------------------------
<void>
<string></string> -- UI Field Name (odd node #'s)
<string></string> -- UI Field Data (even node #'s)
</void>
-------------------------------------------
</object>
</java>

The <void></void> node repeats 57 times per row for a total of 114 values.

I've had some success with XQuery

SELECT
XML_DATA.VALUE ('('/java/object/void/string)[n]', 'varchar(max)')
FROM
CUSTOMER.CUSTOMER_VALUE_SET

Where n = 1..114 - and this is issue one. While loops don't work because XQuery's don't like subtitution variables so I've had to brute force it to get all of the data .. by repeating the XQUERY 114 times within the select statement.

Right now the output is not what I want. It comes out in the form of:

NODE 1 .. NODE 114 in side by side columns.

What I want is

NODE 1 NODE 3 NODE 5 .. NODE (n-1)
------------------------------------------
NODE 2 NODE 4 NODE 6 .. NODE n

Below is actual data of the first two nodes from one row

<java version="1.5.0_12" class="java.beans.XMLDecoder">
<object class="java.util.HashMap">
<void method="put">
<string>Product[0].Account Information[0].Account Web Page Address</string>
<string />
</void>
<void method="put">
<string>Product[0].Account Information[0].Similar Account Flag</string>
<string>false</string>
</void>
...
</object>
</java>

--replace dbo.[MYTABLE].[XML]/[PK] with your table and column name (column that holds the xml data)
--since there are 57 "Nodes" you are interested in , you could wipe out [Node58] ...[Node60]

;WITH mydata AS
(
SELECT [PK] AS tblrowID, t.col.value('(string/text())[1]', 'varchar(1000)') as theval,
ROW_NUMBER() OVER(PARTITION BY [PK] ORDER BY (SELECT 1)) as rownum
FROM dbo.[MYTABLE]
CROSS APPLY [XML].nodes('java/object/void') AS t(col)
)
SELECT tblrowID, rownum,
[Node1], [Node2], [Node3], [Node4], [Node5], [Node6], [Node7], [Node8], [Node9], [Node10],
[Node11], [Node12], [Node13], [Node14], [Node15], [Node16], [Node17], [Node18], [Node19], [Node20],
[Node21], [Node22], [Node23], [Node24], [Node25], [Node26], [Node27], [Node28], [Node29], [Node30],
[Node31], [Node32], [Node33], [Node34], [Node35], [Node36], [Node37], [Node38], [Node39], [Node40],
[Node41], [Node42], [Node43], [Node44], [Node45], [Node46], [Node47], [Node48], [Node49], [Node50],
[Node51], [Node52], [Node53], [Node54], [Node55], [Node56], [Node57], [Node58], [Node59], [Node60]
FROM
(
select tblrowID, theval, 2-rownum&#37;2 as rownum, 'Node' + CAST(rownum/2 + rownum%2 AS VARCHAR(5)) as aggregator
from mydata
) AS SourceTable
PIVOT
(
MAX(theval)
FOR aggregator IN ( [Node1], [Node2], [Node3], [Node4], [Node5], [Node6], [Node7], [Node8], [Node9], [Node10],
[Node11], [Node12], [Node13], [Node14], [Node15], [Node16], [Node17], [Node18], [Node19], [Node20],
[Node21], [Node22], [Node23], [Node24], [Node25], [Node26], [Node27], [Node28], [Node29], [Node30],
[Node31], [Node32], [Node33], [Node34], [Node35], [Node36], [Node37], [Node38], [Node39], [Node40],
[Node41], [Node42], [Node43], [Node44], [Node45], [Node46], [Node47], [Node48], [Node49], [Node50],
[Node51], [Node52], [Node53], [Node54], [Node55], [Node56], [Node57], [Node58], [Node59], [Node60]
)
) AS PivotTable
ORDER BY tblrowID, rownum
--HTH--

Thank Mikr0s - it's getting me towards the final solution.

I'm new to SQL Server 2005 (and moving to 2008 very soon).