Select specific info from XML field

Question

Post reply

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!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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 · Answer 2

SQL_dummy-431245

SSC-Addicted

Points: 426

November 10, 2014 at 4:59 am

#1757396

WOW!!!

You are the best!!

Thank you very very much!!!!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

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: 6639 More actions · Answer 4

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>