SQL Server Performance

Extracting XML CLOB data

Discussion in 'SQL Server 2005 General Developer Questions' started by narlo, May 7, 2010.

  1. narlo New Member

    I've read with great appreciation S. Srivathsani's article on extracting XML data from SQL Server 2005 CLOBs ("Working with XML Data in SQL Server 2005").
    I was wondering if I could get some help on a similarly related problem extracting the CLOB data on very long rows of SQL Data with a schema that looks like:<java version="1.5.0_12" class="java.beans.XMLDecoder">
    <object class="java.util.HashMap">
    ------------------------------------------- repeats 30 times per row
    <void method="put">
    <string></string>
    <string></string>
    </void>
    -------------------------------------------
    </object>
    </java>The void method repeats 30 times per row and what I want to do is output the first <string> data element as column header 1 and the second <string> data element as data element 1 per <void> and end up with 30 columns and 1 row of data per xml record.
    <string> ..... <string> 30 columns
    <string> .... <string> one record row of 30 fields
    I've been able to extract the xml data per S. Srivathsani's article using this method:SELECT
    X_CLOB.query ('data(/java/object/void/string)') from XML_CUSTOMER_VALUE_SET
    but instead of simply returning string data element 1, it's returning both string elements data and concatenates each element in a single row in the output separated by a space.
    When I use the query argument of ('data(/java/object/void/string/string)') no data is returned
    Any ideas?
    Please note that I did not design this and it's an operation OLTP database where none of the values stored in the 4 XML CLOB tables is being stored in the a normalized table/column structure. I can't change the design
  2. FrankKalis Moderator

    Welcome to the forum!
    Is your problem still current?
  3. narlo New Member

    The answer was simpler than I thought, but just in case anyone else has a similar question, here's the solution that worked for me:
    USE TEMPDB
    --SUBMISSION_ID START--
    DECLARE @RowCNT INT
    SELECT @RowCNT = (SELECT MIN(SUBMISSION_ID) FROM SUBMISSION_VALUE_SET)
    --MAX SUBMISSION_ID----
    DECLARE @MaxRowCNT INT
    SELECT @MaxRowCNT = (SELECT MAX(SUBMISSION_ID) FROM SUBMISSION_VALUE_SET)
    --FIRST CLOB-----------
    DECLARE @XML XML
    SELECT @XML = (SELECT CONVERT(XML,SUBMISSION_CLOB) FROM dbo.SUBMISSION_VALUE_SET WHERE SUBMISSION_ID=@RowCNT)
    --NODES START----------
    DECLARE @NodeCNT INT
    SELECT @NodeCNT =1
    --MAX NODES------------
    DECLARE @MaxNODES INT
    SELECT @MaxNODES = (SELECT @XML.query('count(java/object/void)').value('.','int'))
    --DROP TABLE-----------
    DECLARE @keyPK int;
    SELECT @keyPK = 1;
    IF OBJECT_ID ('MAIDEN_SUBMISSION_DECLOB','U') IS NOT NULL
    drop table MAIDEN_SUBMISSION_DECLOB
    --CREATE TABLE---------
    CREATE TABLE MAIDEN_SUBMISSION_DECLOB(keyPK INT,SUBMISSION_ID INT,fName VARCHAR(MAX),fValue VARCHAR(MAX))
    --Let's Get Some Data
    WHILE @RowCNT <= @MaxRowCNT
    BEGIN
    WHILE
    @NodeCNT <= @MaxNODES
    BEGIN
    INSERT INTO dbo.MAIDEN_SUBMISSION_DECLOB(keyPK, SUBMISSION_ID, fName, fValue)
    SELECT
    @keyPK,
    @RowCNT,
    @XML.value('(java/object/void[position()=sql:variable("@NodeCNT")]/string) [1]','varchar(max)'),
    @XML.value('(java/object/void[position()=sql:variable("@NodeCNT")]/string) [2]','varchar(max)')
    SET @NodeCNT=@NodeCNT+1
    SET @keyPK = @keyPK+1
    END
    SELECT @RowCNT = @RowCNT+1
    SELECT @XML = (SELECT CONVERT(XML,SUBMISSION_CLOB) FROM dbo.SUBMISSION_VALUE_SET WHERE SUBMISSION_ID=@RowCNT)
    SELECT @MaxNODES = (SELECT @XML.query('count(java/object/void)').value('.','int'))
    SELECT @NodeCNT =1
    OPTION (MAXDOP 0)
    END

Share This Page