OpenXML over WDDX (Web Distributed Data eXchange) data

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
YESanyJamesBall67877767689711631004117268767hhkjhkjuk  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

select 
 a.id,
 a.localname, 
 b.id,
 b.parentid,
 b.localname, 
 c.id,
 c.parentid,
 c.localname, 
 d.id,
 d.parentid, 
 d.text as dataproperty, 
 f.text as datavalue
from 
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) a
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) b
 on b.parentid = a.id
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) c
 on c.parentid = b.id
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) d
 on d.parentid = c.id
left join 
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) e
 on e.parentid = b.id
left join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) f
 on f.parentid = e.id
where c.localname = 'name'
  and e.localname <> 'name'

MS XML Parser Checker

Determine version of XML Parser installed

(totally fails if no xml parser is installed!)

http://support.microsoft.com/kb/278674/

USP_DropXMLIndexes

Removes all XML Indexes from a table, takes schema name and table name as parameters.
e.g. EXEC USP_DropXMLIndexes 'Person', 'Contact'

CREATE PROCEDURE USP_DropXMLIndexes
              @schemaname VARCHAR(128),
              @tablename  VARCHAR(128)
             
AS
-- USP_DropXMLIndexes by sql solace
 DECLARE  @sqlstring NVARCHAR(500)
                    
 WHILE EXISTS (SELECT *
               FROM   sys.internal_tables AS IT
                      JOIN sys.tables AS T
                        ON IT.PARENT_ID = T.OBJECT_ID
                      JOIN sys.schemas AS S
                        ON T.SCHEMA_ID = S.SCHEMA_ID
                      JOIN sys.xml_indexes AS XI
                        ON IT.PARENT_ID = XI.OBJECT_ID
                       AND IT.PARENT_MINOR_ID = XI.INDEX_ID
               WHERE  S.NAME = @schemaname
                      AND T.NAME = @tablename)
   BEGIN
     SELECT @sqlstring = 'DROP INDEX [' + XI.NAME + '] ON [' + S.NAME + '].[' + T.NAME + '] '
     FROM   sys.internal_tables AS IT
            JOIN sys.tables AS T
              ON IT.PARENT_ID = T.OBJECT_ID
            JOIN sys.schemas AS S
              ON T.SCHEMA_ID = S.SCHEMA_ID
          JOIN sys.xml_indexes AS XI
              ON IT.PARENT_ID = XI.OBJECT_ID
                 AND IT.PARENT_MINOR_ID = XI.INDEX_ID
     WHERE  S.NAME = @schemaname
            AND T.NAME = @tablename
                        
     PRINT @sqlstring
    
     EXECUTE sp_executesql @sqlstring
   END

GO

2005 : XML Indexes

Find tables with XML Indexes

SELECT  s.name as SchemaName,  t.name as TableName, xi.name as XMLIndexName
FROM sys.internal_tables AS it
JOIN sys.tables AS t
  ON it.parent_id = t.object_id
JOIN sys.schemas as s
on t.schema_id = s.schema_id
JOIN sys.xml_indexes AS xi
  ON it.parent_id = xi.object_id
  AND it.parent_minor_id  = xi.index_id

Finding disabled XML Indexes

SELECT  *
FROM    sys.xml_indexes
WHERE   is_disabled != 0;

Disabling an XML Index

ALTER INDEX indexname ON tablename DISABLE

OPENXML - Data Retrieval 2 (via CTE)

A further OPENXML example to read data from wddxPacket -

I am using recursive functionality of a sql CTE (common table expression) here -

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
    

    
      
        
          a string
        
        
          -12.456
        
        
          1998-06-12T04:32:12
        
        
          
        
        
          
            10
            second element
          
        
        
          
            
              a string
            
            
              -12.456
            
          
        
      
    
  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


;with recursiveCTE(level,id,parentid,nodetype,localname,[text], sortcolumn)
as
(

 SELECT '1',a.id,a.parentid,a.nodetype,a.localname,a.[text] , CAST(a.id AS VARBINARY(900))
    FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1) a

    UNION ALL

 SELECT '2',b.id,b.parentid,b.nodetype,b.localname,b.[text] , CAST(sortcolumn + CAST(b.id AS BINARY(4)) AS VARBINARY(900))
    FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1) b
    INNER JOIN recursiveCTE c on c.id = b.parentid  

)

select * from recursiveCTE
order by sortcolumn

OPENXML - Data Retrieval

Simple OPENXML example to read data from wddxPacket

Note : I have escaped the single quote (') character by replacing it with 2 single quotes ('') to make this work!

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
    

    
      
        
          a string
        
        
          -12.456
        
        
          1998-06-12T04:32:12
        
        
          
        
        
          
            10
            second element
          
        
        
          
            
              a string
            
            
              -12.456
            
          
        
      
    
  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM   OPENXML (@idoc, 'wddxPacket/data/struct', 1)