Problem importing XML

[Katerine]

Hi,
I'm still sort of a newbie with TSQL, and a total newbie to XML, so please bear with me.

It's in SQL Express 2008. Compatibility level is set at 100.

I'm trying to import an XML file into a table. It's able to import the whole thing into a table, but what I want is for it to parse into separate fields. It won't even compile.

The code is more-or-less copied from the SQL Bible (book), which has served me very well in the past. Unfortunately, it doesn't really explain what the "o(h)" and "i(x)" in its example code means, and I can't see anything like it when googling, so I'm kind of coding blind. This is undoubtedly the root of the problem.

The problem is that the text (marked in red below) has the red squigglies in SSMS, so it won't parse. Mousing over "p.value", it says, "cannot find either column 'p' or the user defined function or the aggregate 'p.value', or the name is ambiguous." "r.value" says the same. "p.nodes" says, "Invalid object name 'p.nodes'." Please help!

Here's the code, minus the stored procedure container:

    DECLARE @xml XML
	SELECT @xml = CAST(bulkcolumn AS XML)
	FROM OPENROWSET(BULK 'C:\Users\K\Documents\Database-20120309190505.xml', SINGLE_BLOB) AS p
	
	INSERT INTO dbo.IMPORT_Fields(PageID, PageTitle, RevisionTimestamp, RevisionText)
	SELECT
		[COLOR=#ff0000]p.value[/color]('@id','INT') AS PageID,
		[COLOR=#ff0000]p.value[/color]('@title','VARCHAR(250)') AS PageTitle,
		[COLOR=#ff0000]r.value[/color]('@timestamp','VARCHAR(50)') AS RevisionTimestamp,
		[COLOR=#ff0000]r.value[/color]('@text','NTEXT') AS RevisionText
	FROM @xml.nodes('/mediawiki/page') o(p)
	CROSS APPLY [COLOR=#ff0000]p.nodes[/color]('revision') i(r)

Thanks!

Katie

 

[gmmastros]

Can you post a sample of what the XML looks like?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Katerine]

Ok... sorry, didn't think it could be relevant, given the nature of the issue. )

<mediawiki xmlns="[URL unfurl="true"]http://www.mediawiki.org/xml/export-0.4/"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xsi:schemaLocation="[URL unfurl="true"]http://www.mediawiki.org/xml/export-0.4/[/URL] [URL unfurl="true"]http://www.mediawiki.org/xml/export-0.4.xsd"[/URL] version="0.4" xml:lang="en">
  <siteinfo>
    <sitename>M Database</sitename>
    <base>[URL unfurl="true"]http://somesite.wikia.com/Main_Page</base>[/URL]
    <generator>MediaWiki 1.16.5</generator>
    <case>first-letter</case>
    <namespaces>
      <namespace key="-2" case="first-letter">Media</namespace>
    </namespaces>
  </siteinfo>
  <page>
    <title>Here is a title</title>
    <id>8143</id>
    <revision>
      <id>1832507</id>
      <timestamp>2012-01-03T21:04:52Z</timestamp>
      <contributor>
        <username>Somebody</username>
        <id>4591819</id>
      </contributor>
      <text xml:space="preserve">{{Template
| Image               = this.jpg
| Month               = December
| Year                = 2001

Snipping all of the rest. Lots and lots of paragraphs worth of stuff.
}}
[[Category:TB]]</text>
    </revision>
  </page>
  [i]<page>
    ...
  </page>
  (etc..)[/i]
</mediawiki>

Note: since the original post, I've got a couple of self-corrections:
Corrected code (replaced NTEXT with VARCHAR(MAX) (in the destination table as well):

    DECLARE @xml XML
    
	SELECT @xml = CAST(bulkcolumn AS XML)
	FROM OPENROWSET(BULK 'C:\Users\K\Documents\Database-20120309190505.xml', SINGLE_BLOB) AS p
	
	DELETE FROM dbo.IMPORT_Fields
	
	INSERT INTO dbo.IMPORT_Fields(PageID, PageTitle, RevisionTimestamp, RevisionText)
	SELECT
		p.value('@id','INT') AS PageID,
		p.value('@title','VARCHAR(250)') AS PageTitle,
		r.value('@timestamp','VARCHAR(50)') AS RevisionTimestamp,
		r.value('@text','VARCHAR(MAX)') AS RevisionText
	FROM @xml.nodes('/mediawiki/page') o(p)
	CROSS APPLY p.nodes('revision') i(r)

It turns out it does compile - that is, I'm able to execute the CREATE PROCEDURE code. Even though IntelliSense is still showing those... I guess I should call them, "warnings," not "errors." And when I execute the stored procedure, it doesn't give an error, it just returns 0 (no errors). It does not, however, insert any rows into the table.

Thanks again for any help!

Katie

 

[Katerine]

Just bumping, since it's been a few weeks... does anybody have any ideas?

It's not just this. I really need to know how to read an XML file into a table in general, not just for this.

Thanks again for any help.

Katie