Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  open xml parsing error ns1

Author  Topic  ahmedh Starting Member 1 Post Posted - 2014-12-16 : 05:14:57 have an xml file: <ns1:TICD_Study_Data_Extract xmlns:ns1="http://www.abc.com"> <ns1:Practice>C82007</ns1:Practice> <ns1:Extraction_Date>20141015</ns1:Extraction_Date> <ns1:Extracted_Data> <ns1:Demog_data_from_EXTRACTDB> <ns1:REFERENCE>41496</ns1:REFERENCE> <ns1:AGE>29</ns1:AGE> etc I ran this sucessfuly: USE [ticd] GO CREATE TABLE XMLwithOpenXML ( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME ) INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK 'C:\Documents and Settings\Administrator\Desktop\C82007_15.10.2014.xml', SINGLE_BLOB) AS x; SELECT * FROM XMLwithOpenXML but my openxml statment is not parsing i think the root I have is wrong?: USE [ticd] GO DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) SELECT @XML = XMLData FROM dbo.XMLwithOpenXML -- PRINT CAST(@XML as nvarchar(max)) EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<root "xmlns:ns1=http://www.ukaguzi.com"/>' SELECT [ns1:AGE],[ns1:SEX], [ns1:REGISTERED_DATE] FROM OPENXML(@hDoc, 'ns1:TICD_Study_Data_Extract/ns1:Extracted_Data') WITH ( [ns1:AGE] [nvarchar](100) '@Age', [ns1:SEX] [nvarchar](100) '@Sex', [ns1:REGISTERED_DATE][nvarchar](100) '@reg' ) EXEC sp_xml_removedocument @hDoc GO new to xml any help would be appreciated