Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

XML to Table

Modified: 2008/04/09 09:41 by arjan - Categorized as: SQL Server


Edit

Code snippet to convert XML to a table:

Edit

Example 1:

SQL:
declare @xml as xml
set @xml = '
    <element id="1" item="evident" />
    <element id="2" item="interactive" />
    <element id="3" item="utrecht" />
'

select ref.value('@id', 'int') as Id, ref.value('@item', 'nvarchar(32)') as Item from @xml.nodes('//element') AS node(ref)

The result is:
IdItem
1evident
2interactive
3utrecht

Edit

Example 2:

SQL:
declare @xml2 as xml
set @xml2 = '
    <element id="4">
        <item>microsoft</item>
    </element>
    <element id="5">
        <item>gold</item>
    </element>
    <element id="6">
        <item>partner</item>
    </element>
'

select ref.value('@id', 'int') as Id, ref.value('item[1]', 'nvarchar(32)') as Item from @xml2.nodes('//element') AS node(ref)

The result is:
IdItem
4microsoft
5gold
6partner

 © Evident Interactive BV