Results 1 to 6 of 6

Thread: Help With Transactional Data stored in XML CLOBs

  1. #1
    Join Date
    May 2010
    Posts
    5

    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?

  2. #2
    Join Date
    Sep 2005
    Posts
    168
    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.

  3. #3
    Join Date
    May 2010
    Posts
    5

    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.

  4. #4
    Join Date
    May 2010
    Posts
    5
    <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>

  5. #5
    Join Date
    Sep 2005
    Posts
    168
    --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--
    Last edited by mikr0s; 05-09-2010 at 08:41 AM.

  6. #6
    Join Date
    May 2010
    Posts
    5
    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
  •