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
   

- Advertisement -