How to read XML from a column in table (Network Steve Forum)

How to read XML from a column in table

Hi,

I have the follow XML stored in a SQL table,

<html><head><style type="text/css" /></head><body><data>
<timesheets><customerid>1</customerid><customername>Client 1</customername><billable>False</billable></timesheets>
<timesheets><customerid>2</customerid><customername>Client 2</customername><billable>False</billable></timesheets>
<timesheets><customerid>3</customerid><customername>Client 3</customername><billable>False</billable></timesheets>
</data>
</body></html>

Table layout

create table xmltest

(data xml),

how can i get this data as a grid?

Customerid customername, billable
1 Client 1 False
2 Client 2 False
3 Client 3 False

Can somebody help me?

Thanks in advance

June 22nd, 2015 3:15pm

I think you already posted this question.. copying my answer from the previous post...

declare @xmltable table(
id int identity(1,1),
dataxml xml)
declare @xmldata xml

SET @xmldata = '<html><head><style type="text/css" /></head><body><data>
<timesheets><customerid>1</customerid><customername>Client 1</customername><billable>False</billable></timesheets>
<timesheets><customerid>2</customerid><customername>Client 2</customername><billable>False</billable></timesheets>
<timesheets><customerid>3</customerid><customername>Client 3</customername><billable>False</billable></timesheets>
</data>
</body></html>'

insert into @xmltable values (@xmldata)
select Dataxml from @xmltable

select id, 
       D.C.value('./customerid[1]', 'int') as customerid,
       D.C.value('./customername[1]', 'varchar(50)') as customername,
       D.C.value('./billable[1]', 'varchar(30)') as billable
  from @xmltable
  cross apply dataxml.nodes('/html/body/data/timesheets') D(c)

old thread : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb850bee-c6d7-412d-a53a-94ec35356e20/error-with-xml?forum=sqlxml#b21dea48-dd6f-4609-b164-ba1ea691da12

Free Windows Admin Tool Kit Click here and download it now

June 22nd, 2015 4:47pm

This topic is archived. No further replies will be accepted.