Parse and Import XML into Table in SQL Server

Question

Hi I've written a CLR assembly that exports a table's data to an XML file. Now I want to import this data into a temp table on another instance. The XML file structure is like this:

<row>
  <SystemInformationID>1</SystemInformationID>
  <Database_x0020_Version>10.00.80404.00</Database_x0020_Version>
  <VersionDate>2008-04-04T00:00:00</VersionDate>
  <ModifiedDate>2008-04-04T00:00:00</ModifiedDate>
</row>

I want the XML to be parsed in the destination location and imported into a temp table. I have the main table there too, so I can get the table structure from there. Is there a way? I use OPENXML but it seems not to be working correctly. I can read the XML file into a table, which will be stored in a column with XML data type. My problem is parsing the data in that column. This is a temp attempt:

CREATE TABLE ##T (IntCol int, XmlCol xml)
GO

INSERT INTO ##T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'c:\HISOutput.xml',
   SINGLE_CLOB) AS x
--works correctly up to this point

DECLARE @x xml
DECLARE @id int
SELECT @x=XmlCol FROM ##T

EXEC sp_xml_preparedocument @id OUTPUT, @x

SELECT    *
FROM       OPENXML (@id,'/row',2)
WITH 
dbo.awbuildversion

--I used dbo.awbuildversion table from AdventureWorks DB for testing
this doesn't show the first column no matter how I change the OPENXML instruction.

tx in advance

Answer 1

I'm not quite sure what you want, because your comment to OMG Ponies' answer is different to your question. What is the problem with temp table/table structure?

Anyway, I wouldn't use OPENXML or sp_xml_preparedocument on SQL Server 2050 and above (which you have I assume because you mentioned CLR) because of the memory leak risk.

Also, if you need the table stucture then you could use INTO #tempTable

DECLARE @foo xml

SET @foo = '<row>
  <SystemInformationID>1</SystemInformationID>
  <Database_x0020_Version>10.00.80404.00</Database_x0020_Version>
  <VersionDate>2008-04-04T00:00:00</VersionDate>
  <ModifiedDate>2008-04-04T00:00:00</ModifiedDate>
</row>'

SELECT
    bar.value('./SystemInformationID[1]','INT') AS 'SystemInformationID',
    bar.value('./Database_x0020_Version[1]','VARCHAR(14)') AS 'Database_x0020_Version',
    bar.value('./VersionDate[1]','DATETIME') AS 'VersionDate',
    bar.value('./ModifiedDate[1]','DATETIME') AS 'ModifiedDate'
INTO #tempTable    -- This?
FROM
    @foo.nodes('/row') AS foo(bar)     --use nodes not OPENXML