Select from xml field-Please Please and Please Help!!

Question

Hi,

I have the below xml field need to read.

create table xmltable (id int, xmlField xml)
go
delete from xmltable
declare @xml xml
set @xml = '<?xml version="1.0" encoding="UTF-8"?>
<MyData>
	<Layer1>

	  <Fruits>
		<Fruit>Apple</Fruit>
		<Color>
			<ColorEnum>Yellow</ColorEnum>
			<ColorEnum>Green</ColorEnum>
		</Color>
	 </Fruits>

	 <Fruits>
		<Fruit>Berry</Fruit>
		<Color>
			<ColorEnum>Black</ColorEnum>
			<ColorEnum>Red</ColorEnum>
		</Color>
	</Fruits>

  </Layer1>
</MyData>
'

insert into xmltable values (1,@xml)

I need to write query to return the below result from the xml field on the table. How to do it? Thanks.

Fruit  Color
------------------------
Apple  Yellow
Apple  Green
Berry   Black
Berry   Red

---

Sea Cloud

Answer 1

Try this:

USE tempdb
go

create table xmltable (id int, xmlField xml)
go

insert into xmltable
select 1, '<?xml version="1.0" encoding="UTF-8"?>
<MyData>
  <Layer1>
    <Fruits>
      <Fruit>Apple</Fruit>
      <Color>
        <ColorEnum>Yellow</ColorEnum>
        <ColorEnum>Green</ColorEnum>
      </Color>
    </Fruits>
    <Fruits>
      <Fruit>Berry</Fruit>
      <Color>
        <ColorEnum>Black</ColorEnum>
        <ColorEnum>Red</ColorEnum>
      </Color>
    </Fruits>
  </Layer1>
</MyData>'
go

select 
	t.id, 
	f.c.value('.', 'VARCHAR(30)') fruit,
	c.c.value('.', 'VARCHAR(30)') color
from xmltable t
	cross apply t.xmlField.nodes('MyData/Layer1/Fruits') d(c)
		cross apply d.c.nodes('Fruit') f(c)
		cross apply d.c.nodes('Color/ColorEnum') c(c)


drop table xmltable
go

Answer 2

Great script it works beautifully. Thanks a lot.

---

Sea Cloud