November 10, 2014 at 3:54 am
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!!
November 10, 2014 at 4:30 am
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
November 10, 2014 at 4:59 am
WOW!!!
You are the best!!
Thank you very very much!!!!
November 10, 2014 at 12:32 pm
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!
November 10, 2014 at 1:47 pm
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)')
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy