Here is a question on XQuerying in SQL (SQL Server 2008).
I have an XML that I need to output somewhat differently, for reference:


Code:
<?xml version="1.0"encoding="UTF-8"standalone="true"?>
        <Computer >
        	<OS Name="Microsoft Windows 7 Ultimate" 
                  ServicePack="1"/>OS - Category, Namem, Service Pack - Property, 
        	<HardDisks>	
                HardDisks - Category, Value - Save as Name in the Property table
        	     <HardDisk Value="C:\ (NTFS)"/>
        	</HardDisks>
        	<LocalGroups>	
        		<Group Name="Administrators"> LocalGroups - Category, Name - Property
        			<Member Name="Administrator"/> Member - Category, Administrator, whargrove - Property, Administrators - Parent
        			<Member Name="whargrove"/>
        		</Group>
        		<Group Name="Backup Operators">
        		</Group>
        		<Group Name="Guests">
        			<Member Name="Guest"/>
        		</Group>
        		<Group Name="IIS_IUSRS"> LocalGroups - Category, IIS_USERS - Property
        			<Member Name="IUSR"/> LocalGroups - Member, IUSER - Property, IIS_USERS - Parent
        		</Group>
        		    		<Group Name="boinc_projects">
    </Group>
        		    	</LocalGroups>
        </Computer>
Need to insert data like below into my table from above xml.
Here is a one special case i.e If the node contains child node need to insert parent node as parent in the table Parent column if child not exist need to insert NULL.


Code:
Category     Property      Value             Parent
        OS             Name         WH&              NULL
        HardDisks      Null         Null             Null
        HardDisk       Value        C:\ (NTFS)       HardDisks
        LocalGroups    Null         Null             Null
        Group          Name         Administrators   LocalGroups
        Member         Aministrator  Null            LocalGroups
        Member         whargrove     Null            LocalGroups
My query is :

Code:
insert into AssetProperty (Category, Property, Value, Parent)				
			 select
					T.C.value('local-name(.)', 'nvarchar(max)') as  Category,
					A.C.value('local-name(.)', 'nvarchar(max)') as  Property,
					A.C.value('.',			   'nvarchar(max)')	as Value,											     
				    T.C.value('local-name(.)', 'varchar(max)') as Parent				
			   from @XMLdata.nodes('Computer//*') as T(C)
	    outer apply T.C.nodes('./@*') as A(C)
Any solution for this?