Select specific info from XML field

• SQL_dummy-431245

  SSC-Addicted

  Points: 426

  More actions

  November 10, 2014 at 3:54 am

  #74504

  Hello,

  I have a table (InfoTable) with a field with XML info like this:

  <Status>

  <IdStatus>1<IdStatus>

  <NomStatus>Status 1<NomStatus>

  <IdStatus>2<IdStatus>

  <NomStatus>Status 2<NomStatus>

  <IdStatus>3<IdStatus>

  <NomStatus>Status 3<NomStatus>

  ....

  </Status>

  <Process>

  <IdProcess>1</IdProcess>

  <NomProcess>Process 1</NomProcess>

  <IdProcess>2</IdProcess>

  <NomProcess>Process 2</NomProcess>

  <IdProcess>3</IdProcess>

  <NomProcess>Process 3</NomProcess>

  ....

  </Process>

  I need to be able to do the equivalent of: "select NomProcess where IdProcess=3" and get "Process 3" , for instance.

  How can I do it?

  Thanks in advance!!

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  November 10, 2014 at 4:30 am

  #1757391

  Quick note, this xml structure doesn't make things easier;-)

  

  Here is an example that should get you passed this hurdle

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @TXML XML = N'<Status><IdStatus>1</IdStatus>

  <NomStatus>Status 1</NomStatus>

  <IdStatus>2</IdStatus>

  <NomStatus>Status 2</NomStatus>

  <IdStatus>3</IdStatus>

  <NomStatus>Status 3</NomStatus>

  </Status>

  <Process>

  <IdProcess>1</IdProcess>

  <NomProcess>Process 1</NomProcess>

  <IdProcess>2</IdProcess>

  <NomProcess>Process 2</NomProcess>

  <IdProcess>3</IdProcess>

  <NomProcess>Process 3</NomProcess>

  </Process>'

  SELECT

  ROW_NUMBER() OVER

  (

  PARTITION BY (SELECT NULL)

  ORDER BY (SELECT NULL)

  ) AS PR_RID

  ,IDPROCESS.DATA.value('.[1]','INT')

  ,NOMPROCESS.DATA.value('.[1]','VARCHAR(25)')

  FROM @TXML.nodes('Process/IdProcess') as IDPROCESS(DATA)

  CROSS APPLY @TXML.nodes('Process/NomProcess') as NOMPROCESS(DATA)

  WHERE CONVERT(INT,RIGHT(NOMPROCESS.DATA.value('.[1]','VARCHAR(25)'),1),1) = IDPROCESS.DATA.value('.[1]','INT')

  Results

  PR_RID

  -------------------- ----------- -----------

  1 1 Process 1

  2 2 Process 2

  3 3 Process 3

• SQL_dummy-431245

  SSC-Addicted

  Points: 426

  More actions

  November 10, 2014 at 4:59 am

  #1757396

  WOW!!!

  You are the best!!

  Thank you very very much!!!!

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  November 10, 2014 at 12:32 pm

  #1757531

  SQL_dummy-431245 (11/10/2014)

  ---

  WOW!!!

  You are the best!!

  Goes without saying:-D

  Thank you very very much!!!!

  Glad to help, just realize that this is a limited solution, only single digit joining between the different nodes, will break if number of nodes are greater than 9! Seriously suggest that the xml structure is revised if possible!

  

• Arthur Olcot

  SSCertifiable

  Points: 6750

  More actions

  November 10, 2014 at 1:47 pm

  #1757551

  Here is another method which gets the NomProcess node immediately following the IdProcess node of the value that you specify

  DECLARE @xml XML

  SET @xml = '<Status>

  <IdStatus>1</IdStatus>

  <NomStatus>Status 1</NomStatus>

  <IdStatus>2</IdStatus>

  <NomStatus>Status 2</NomStatus>

  <IdStatus>3</IdStatus>

  <NomStatus>Status 3</NomStatus>

  </Status>

  <Process>

  <IdProcess>3</IdProcess>

  <NomProcess>Process 3</NomProcess>

  <IdProcess>2</IdProcess>

  <NomProcess>Process 2</NomProcess>

  <IdProcess>1</IdProcess>

  <NomProcess>Process 1</NomProcess>

  </Process>

  '

  DECLARE @NodeValue INT = 3

  SELECT @xml.value('(/Process/NomProcess[ . >> (/Process/IdProcess[text() = sql:variable("@NodeValue")] )[1] ])[1]', 'VARCHAR(100)')