How to get unique Node IDs of a XML?

• ganeshmuthuvelu

  SSCrazy

  Points: 2177

  More actions

  May 19, 2008 at 3:58 pm

  #187772

  I have a XML as below

  declare @doc2 xml

  SET @doc2 = '

  <ROWS>

  <COL1>1</COL1>

  <COL2>2</COL2>

  <COL3>3</COL3>

  </ROWS>

  <ROWS>

  <COL1>11</COL1>

  <COL2>22</COL2>

  <COL3>33</COL3>

  </ROWS>'

  Select r.value('local-name(.)','varchar(20)') as Name,

  r.value('(text())[1]','varchar(100)') as Value

  From @doc2.nodes('//*') as x(r)

  This works to return the node values but is there a way to return UNIQUE ID for each NOde?.

  Thanks

  Ganesh

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 19, 2008 at 4:38 pm

  #817515

  ganeshmuthuvelu (5/19/2008)

  ---

  I have a XML as below

  declare @doc2 xml

  SET @doc2 = '

  <ROWS>

  <COL1>1</COL1>

  <COL2>2</COL2>

  <COL3>3</COL3>

  </ROWS>

  <ROWS>

  <COL1>11</COL1>

  <COL2>22</COL2>

  <COL3>33</COL3>

  </ROWS>'

  Select r.value('local-name(.)','varchar(20)') as Name,

  r.value('(text())[1]','varchar(100)') as Value

  From @doc2.nodes('//*') as x(r)

  This works to return the node values but is there a way to return UNIQUE ID for each NOde?.

  Thanks

  Ganesh

  Ganesh - what in your example are you calling the UNIQUE ID? I'm not seeing anything unique in what you have there.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• ganeshmuthuvelu

  SSCrazy

  Points: 2177

  More actions

  May 19, 2008 at 4:45 pm

  #817519

  An auto generated number something like the node "position()" is what I am looking for to uniquely idenitfy the node value..

  The reason I need is, after getting the node values I want to be able to relate them somehow, so that I know which value came from which node..

  Right now, the query returns like this:

  Name,Value

  ROWS,NULL

  COL1,1

  COL2,2

  COL3,3

  ROWS,NULL

  COL1,11

  COL2,22

  COL3,33

  Instead, I want like this (note the Node ID value that changes from 1 to 2):

  Name,Value, NODEID

  ROWS,NULL,1

  COL1,1,1

  COL2,2,1

  COL3,3,1

  ROWS,NULL,2

  COL1,11,2

  COL2,22,2

  COL3,33,2

  Hope it is clear.

  Thanks,

  Ganesh

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 19, 2008 at 5:08 pm

  #817521

  ganeshmuthuvelu (5/19/2008)

  ---

  An auto generated number something like the node "position()" is what I am looking for to uniquely idenitfy the node value..

  The reason I need is, after getting the node values I want to be able to relate them somehow, so that I know which value came from which node..

  Right now, the query returns like this:

  Name,Value

  ROWS,NULL

  COL1,1

  COL2,2

  COL3,3

  ROWS,NULL

  COL1,11

  COL2,22

  COL3,33

  Instead, I want like this (note the Node ID value that changes from 1 to 2):

  Name,Value, NODEID

  ROWS,NULL,1

  COL1,1,1

  COL2,2,1

  COL3,3,1

  ROWS,NULL,2

  COL1,11,2

  COL2,22,2

  COL3,33,2

  Hope it is clear.

  Thanks,

  Ganesh

  Sorry to say - not as easy as it might seem. position() can only be used in criteria/xquery/xpath, and cannot be used to RETURN the position. Well - not directly. The trick to use is over here:

  http://milambda.blogspot.com/2008/04/sql-server-xqueryxpath-predicate.html

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  May 19, 2008 at 8:00 pm

  #817546

  ideally, you should transform the doc before processing it and use XSLT's position() to generate the desired attribute.