Query All Elements and Attributes from an XML Column in a table without knowing the Elements or Attributes

i have column in table xml. i'd pull out elements , attributes out of xml column.

my table name is: reportdefinition

my columns are: reportname, contentxml

i'd have output follows:

reportname,  element (from contentxml,  attribute (from contentxml)

there may number of elements and attributes in contentxml column

i'm not how create syntax extract value

but know on right track:

select 	x.y.value('local-name(..)', 'varchar(max)') parentelementname, 	x.y.value('local-name(.)', 'varchar(max)'),     x.y.value('.', 'varchar(max)') @xml.nodes('//*[text()], //@*') x(y)

---

john

use cross apply against xml column shred it, eg

declare @reportdefinition table ( reportname varchar(50), contentxml xml )  insert @reportdefinition values ('report1', '<report> 	<a b="c">d</a> 	<f g="h">i</f> </report>' ), ('report2', '<report> 	<d x="y">z</d> 	<e l="m">n</e> </report>' )  select 	reportname, 	x.y.value('local-name(..)', 'varchar(max)') parentelementname, 	x.y.value('local-name(.)', 'varchar(max)'),     x.y.value('.', 'varchar(max)') @reportdefinition rd 	cross apply rd.contentxml.nodes('//*[text()], //@*') x(y)

SQL Server  >  SQL Server XML