Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pldelosrios
Starting Member
4 Posts |
 Posted - 2014-11-10 : 04:34:32
|
| 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>Status 1</NomProcess> <IdProcess>2</IdProcess> <NomProcess>Status 2</NomProcess> <IdProcess>3</IdProcess> <NomProcess>Status 3</NomProcess> ....</Process>I want to be able to select then NomProcess where IdProcess=3, for instance.How can I do it?Thanks in advance!! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 09:10:11
|
Your XML is invalid. On top of that, there is no hierarchical relationship between the elements IdProcess and NomProcess. However I was able to get your desired result. This assumes that the sequence of elements is strict -- that is that you will always have IdProcess followed by NomProcess:DECLARE @xml XML = '<root><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>Status 1</NomProcess><IdProcess>2</IdProcess><NomProcess>Status 2</NomProcess> <IdProcess>3</IdProcess><NomProcess>Status 3</NomProcess>....</Process></root>'SELECT x.value('.', 'Nvarchar(20)')from @xml.nodes('/root/Process/NomProcess[position() = 1]') x(x) |
 |
|
|
|
|
|
Select specific info from XML field