how to retreive the data values from the xml data tree nodes

• srirohanm2001

  Newbie

  Points: 3

  More actions

  June 10, 2009 at 7:15 am

  #97665

  i am trying to pull data from the xml data tree given below

  got the values for all the nodes except for the "Unit" node having value "511" ,pls help

  similarly Lease ,Tenant give me the concatenation of the subnode values but not "511"

  1

  1

  0

  10500

  10885072681 Tenant 1

  i tried this...

  select cfgxmlid,

  RTRIM(NewTable.LOOKUPS.value('Unit[1]','VARCHAR(20)')) As 'Unit',

  RTRIM(NewTable.LOOKUPS.value('SFSUnitID[1]','VARCHAR(20)')) As 'SFSUnitID',

  RTRIM(NewTable.LOOKUPS.value('UnitID[1]','VARCHAR(20)')) As 'UnitID',

  RTRIM(NewTable.LOOKUPS.value('NumberOfUnits[1]','VARCHAR(20)')) As 'NumberOfUnits',

  RTRIM(NewTable.LOOKUPS.value('Lease[1]','VARCHAR(20)')) As 'Lease',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/SFSLeaseID)[1]','VARCHAR(20)') as 'SFSLeaseID',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/Tenant)[1]','VARCHAR(150)') as 'Tenant'

  FROM DEV..CFGXML CROSS APPLY XMLstring.nodes('//CREProject/InvProject/Property/Unit') AS NewTable(LOOKUPS)

  i get this..

  

  as you can see the unit column contains all NULL values, it should have values like "511"

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 10, 2009 at 7:36 am

  #1007387

  What are you trying to get out of it and what query do you have so far? Also, for future reference, please wrap XML in code tags, so it shows on the forum.

  1

  1

  0

  10500

  10885072681

  Tenant 1

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• srirohanm2001

  Newbie

  Points: 3

  More actions

  June 11, 2009 at 2:44 am

  #1007840

  i am trying to pull data from the xml data tree given below

  got the values for all the nodes except for the "Unit" node having value "511" ,pls help

  similarly Lease ,Tenant give me the concatenation of the subnode values but not "511"

  1

  1

  0

  10500

  10885072681

  Tenant 1

  i tried this...

  select cfgxmlid,

  RTRIM(NewTable.LOOKUPS.value('Unit[1]','VARCHAR(20)')) As 'Unit',

  RTRIM(NewTable.LOOKUPS.value('SFSUnitID[1]','VARCHAR(20)')) As 'SFSUnitID',

  RTRIM(NewTable.LOOKUPS.value('UnitID[1]','VARCHAR(20)')) As 'UnitID',

  RTRIM(NewTable.LOOKUPS.value('NumberOfUnits[1]','VARCHAR(20)')) As 'NumberOfUnits',

  RTRIM(NewTable.LOOKUPS.value('Lease[1]','VARCHAR(20)')) As 'Lease',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/SFSLeaseID)[1]','VARCHAR(20)') as 'SFSLeaseID',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/Tenant)[1]','VARCHAR(150)') as 'Tenant'

  FROM DEV..CFGXML CROSS APPLY XMLstring.nodes('//CREProject/InvProject/Property/Unit') AS NewTable(LOOKUPS)

  i get this..

  

  5254NULL11 10885072681Tenant 1 10885072681Tenant 14020240199

  5254NULL21 10885072682Tenant 2 10885072681Tenant 14020240199

  5254NULL31 10885072682Tenant 2 10885072681Tenant 14020240199

  as you can see the unit column contains all NULL values, it should have values like "511"

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 11, 2009 at 3:34 am

  #1007855

  Hi,

  just change your line

  RTRIM(NewTable.LOOKUPS.value('Unit[1]','VARCHAR(20)')) As 'Unit',

  to

  RTRIM(NewTable.LOOKUPS.value('../Unit[1]','VARCHAR(20)')) As 'Unit',

  Reason: Your trying to include an element of the previous level. So you have to "climb up" the same way as you "go down" to elements with higher nest elvel.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• srirohanm2001

  Newbie

  Points: 3

  More actions

  June 11, 2009 at 4:37 am

  #1007883

  Hi thanks for ur reply , i tried ur line of code (../Unit[1]) but it brought the concatenated string of the subnode values. Anyways i got what i wanted when i tried this...

  select cfgxmlid,

  RTRIM(NewTable.LOOKUPS.value('@ID[1]','VARCHAR(20)')) As 'Unit',

  RTRIM(NewTable.LOOKUPS.value('SFSUnitID[1]','VARCHAR(20)')) As 'SFSUnitID',

  RTRIM(NewTable.LOOKUPS.value('UnitID[1]','VARCHAR(20)')) As 'UnitID',

  RTRIM(NewTable.LOOKUPS.value('NumberOfUnits[1]','VARCHAR(20)')) As 'NumberOfUnits',

  RTRIM(NewTable.LOOKUPS.value('@ID[1]','VARCHAR(20)')) As 'Lease',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/@ID)[1]','VARCHAR(20)') as 'Lease',

  XMLstring.value ('(/MasterSchemaRoot/CFG/CREProject/InvProject/Property/Unit/Lease/Tenant/@ID)[1]','VARCHAR(20)') as 'Tenant'

  FROM DEV..CFGXML CROSS APPLY XMLstring.nodes('//CREProject/InvProject/Property/Unit') AS NewTable(LOOKUPS)