1

I have an xml file which shows data like this:

<learner>
   <name>Smith</name>
   <ULN>123456</ULN>
</learner>
<learner>
   <name>Jones</name>
   <ULN>56789</ULN>
</learner>

I have a table that stores the files as varchar (max) as I cannot upload directly as xml from my front end system.

I am able to read the file as an xml file by creating a table:

declare @ILRDATA table (Data xml) 

Insert into @ILRDATA (Data)
select FileUpload from ILRDATA.dbo.ILRUpload

select * from @ILRDATA

I now want to create a @table with the columns (Name varchar (50), ULN varchar (10))

I want to then populate this with the xml data

Can someone please help me before I waste a whole day trying to figure this out.

Thanks

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
gillers322
  • 249
  • 4
  • 17
  • Please check this [link](http://stackoverflow.com/questions/4815836/how-do-you-read-xml-column-in-sql-server-2008), i think it contains what you want. – Majdi Apr 26 '15 at 12:04
  • are you saying you _don't_ want to use the SQL Server XML datatype? – Nick.Mc Apr 26 '15 at 13:09

1 Answers1

2
select
    t.c.value('name[1]', 'nvarchar(50)') as name,
    t.c.value('ULN[1]', 'nvarchar(10)') as ULN
from @ILRDATA as d
    outer apply d.Data.nodes('learner') as t(c)
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Hi Roman - I just get one line with null in each column. When I replace select FileUpload from ILRDATA.dbo.ILRUpload with the actual data I get the data display but this is not helpful as the actually uploaded file will change. – gillers322 Apr 26 '15 at 18:56
  • here's SQL fiddle with example - http://sqlfiddle.com/#!6/8a9fc/2. Please check that data in the FileUpload is exact the same you've shown in the question – Roman Pekar Apr 26 '15 at 20:23