open xml help parsing error

• ayh1

  Mr or Mrs. 500

  Points: 581

  More actions

  December 16, 2014 at 2:59 am

  #313878

  I 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