I currently have this code which stores XML into an XML-type column called data, in a table called Storage.
CREATE TABLE Storage
(
ID INT IDENTITY(1,1) PRIMARY KEY,
data XML NOT NULL
)
GO
INSERT 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>');
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:
Previously, if I had shredded into tables this would be the code I use:
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)
EDIT2: This is the output I expect.
You need to use CROSS APPLY
SELECT
TeamName = Foot.value('(name)[1]', 'varchar(100)'),
Manager = Foot.value('(@manager)', 'varchar(100)'),
Ground = Foot.value('(ground)[1]', 'varchar(100)')
FROM
Storage S
CROSS APPLY data.nodes('/footballteams/team') AS Tbl(Foot)
CROSS APPLY
and OUTER APPLY
allow you to effectively join to a collection of XML in the current data set.