liquidchaz
New Member
I currently have this code which stores XML into an XML-type column called data, in a table called Storage.\[code\] CREATE TABLE Storage( ID INT IDENTITY(1,1) PRIMARY KEY, data XML NOT NULL)GOINSERT INTO Storage(data) VALUES('<footballteams> <team manager="Benitez"> <name>Liverpool</name> <ground>Anfield</ground> </team> <team manager="Mourinho"> <name>Chelsea</name> <ground>Stamford Bridge</ground> </team> <team manager="Wenger"> <name>Arsenal</name> <ground>Highbury</ground> </team> </footballteams>'); \[/code\]I would like to create a view called Football View which shreds the data and displays it in the form: FootballView(TeamName,Manager,Ground).I have shredded full documents using the .nodes() method into table columns before, but it seems to be more challenging when creating a view (I have my reasons for using views). The problem is that previously I just called .nodes on a variable @input which was DECLARE'd as xml = 'xmlcontent' but with views this can't be done, and I want to parse XML contained within the Storage table column.Any ideas? Thanks in advance.EDIT
reviously, if I had shredded into tables this would be the code I use:\[code\]SELECT TeamName = Foot.value('(name)[1]', 'varchar(100)'), Manager = Foot.value('(@manager)', 'varchar(100)'), Ground = Foot.value('(ground)[1]', 'varchar(100)') FROM @input.nodes('/footballteams/team') AS Tbl(Foot)\[/code\]EDIT2: This is the output I expect.
reviously, if I had shredded into tables this would be the code I use:\[code\]SELECT TeamName = Foot.value('(name)[1]', 'varchar(100)'), Manager = Foot.value('(@manager)', 'varchar(100)'), Ground = Foot.value('(ground)[1]', 'varchar(100)') FROM @input.nodes('/footballteams/team') AS Tbl(Foot)\[/code\]EDIT2: This is the output I expect.