-
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?
Last edited by mikr0s; 05-08-2010 at 08:56 AM.
-
Progress & Example
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%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--
Last edited by mikr0s; 05-09-2010 at 08:41 AM.
-
Thank Mikr0s - it's getting me towards the final solution.
I'm new to SQL Server 2005 (and moving to 2008 very soon).
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|