CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsqlxml
gillers322
gillers322

Reputation: 249

Showing XML Data in SQL table

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

Upvotes: 1

Views: 94

Answers (1)

roman
roman

Reputation: 117540

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)

Upvotes: 2

Related Questions