read an xml datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

read an xml datatype

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-10-25 : 05:20:31

hi,
Can somebody help me extract the xml values from the following xml ?
I have tried various methods such as
select
xmlData.value('/row[1]/@State', 'varchar(10)') [state]
but doesn't work.

my xml is like :
<results>
<data>
<report.cdr rows="5000">
<row state="5" Start="19/10/2010" End="20/10/2010" TranID="133311" />
<row state="6" Start="19/10/2010" End="20/10/2010" TranID="133334" />
</report.cdr>
</data>
</results>

thanks for any help guys.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 06:06:22

Try this -


DECLARE @InXmlDoc AS XML
SELECT @InXmlDoc =
'<results>
<data>
<report.cdr rows="5000">
<row state="5" Start="19/10/2010" End="20/10/2010" TranID="133311" />
<row state="6" Start="19/10/2010" End="20/10/2010" TranID="133334" />
</report.cdr>
</data>
</results>'


        SELECT  b.value('@state','varchar(10)')as UpdateID,
                b.value('@Start','varchar(50)')as DownloadURL,
				b.value('@End', 'varchar(50)') as [End],
				b.value('@TranID', 'varchar(50)') as TranID
        FROM    @InXmlDoc.nodes('/results') as  xmldata(fileds)
                CROSS APPLY fileds.nodes('data/report.cdr/row') as  a(b)