How to insert Parent node text if child node is avilable?

• iramasani 51018

  SSC Veteran

  Points: 252

  More actions

  September 10, 2013 at 3:07 am

  #276967

  Hi,

  I have an XML that I need to output somewhat differently, for reference:

  <?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.

  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 below:

  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?