Showing XML Data in SQL table

Question

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

are you saying you don't want to use the SQL Server XML datatype? — Nick.Mc
– Nick.Mc, Commented Apr 26, 2015 at 13:09

roman · Accepted Answer · 2015-04-26 12:36:59Z

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)

answered Apr 26, 2015 at 12:36

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gillers322 Over a year ago

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.

roman Over a year ago

here's SQL fiddle with example - sqlfiddle.com/#!6/8a9fc/2. Please check that data in the FileUpload is exact the same you've shown in the question

Collectives™ on Stack Overflow

Showing XML Data in SQL table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related