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
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