Rotating Header Image

Parsing XML into a Table Structure… Possible? UPDATE

So going back to

http://benchmarkitconsulting.com/colin-stasiuk/2009/01/14/parsing-xml-into-a-table-structure-possible/

Can it be done?  Yes… it’s not pretty but yes it can be done.  I emailed this to Brent Ozar to see if he was an XML guru as we had gone back on forth on Twitter about whether or not it’s possible.  He pointed me to StackOverflow and 1 day later a user by the name FreddyB solved the problem.

DECLARE @tbl_xml XML 
SET 
@tbl_xml (
  
SELECT @xml_data.query('
      <table>
        {for $elem in /descendant::node()[local-name() != ""] 
        return <row name="{local-name($elem)}">
          {for $attr in $elem/@*
            return <col name="{local-name($attr)}" value="{$attr}" />}
        </row>}
      </table>'
  
)
)

DECLARE @sql_def_tbl VARCHAR(MAX)
SELECT @sql_def_tbl 
  
COALESCE(@sql_def_tbl,'')
    +
'declare @tbl table ('+SUBSTRING(csv,1,LEN(csv)-1)+') '
  
FROM (
    
SELECT (
      
SELECT ''+col.value('@name','varchar(max)')+' varchar(max),'
      
FROM row.nodes('col'r(colFOR XML path('')
    ) 
csv FROM @tbl_xml.nodes('//row[1]'n(row)
  ) 
x

DECLARE @sql_ins_rows VARCHAR(MAX)
SELECT @sql_ins_rows 
  
COALESCE(@sql_ins_rows,'')
    +
'insert @tbl values ('+SUBSTRING(colcsv,1,LEN(colcsv)-1)+') '
  
FROM (
    
SELECT (
      
SELECT ''''+col.value('@value','varchar(max)')+''','
      
FROM row.nodes('col'r(colFOR XML path('')
    ) 
colcsv FROM @tbl_xml.nodes('//row't(row)
  ) 
x

EXEC (@sql_def_tbl @sql_ins_rows 'select * from @tbl')

Now I’m sure that now the base structure is there and works there is probably some optimizations that can be made for performance… but there ya go… it CAN be done.

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. Blackberry Barley Wine channels the elegant spirit of a classic English barley wine, but with a kiss of blackberry to elevate the sip beyond convention

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.