Monday, May 16, 2011

XML into SQL

I'm working on loading an XML file into SQL right now, and struggling a bit with it. Not so much the coding itself, but figuring out how to represent the hierarchical XML data in a relational SQL table.

I did discover this tidbit for taking advantage of SQL's built-in tools for handling XML:

DECLARE @hdoc int DECLARE @doc varchar(max) SELECT @doc = CONVERT(varchar(max), XML_Column) FROM dbo.XML_Table EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc PRINT @hdoc SELECT * INTO #tmp FROM OPENXML (@hdoc, '/RootNodeName',2) EXEC sp_xml_removedocument @hdoc

Works well.

No comments:

Post a Comment