8

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.

Expected Output

1 Answers1

11

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.

Code Magician
  • 23,217
  • 7
  • 60
  • 77
  • Can i ask, is the difference between cross and outer apply that outer will include NULLs too? –  Jun 28 '12 at 13:42
  • 2
    @user1320771 - [Using APPLY](http://msdn.microsoft.com/en-us/library/ms175156(v=sql.105).aspx) is a page on both forms, including the differences. – Damien_The_Unbeliever Jun 28 '12 at 13:44