"Herbert" <Herbert@discussions.microsoft.com> wrote in message
news:A796F68B-DCB5-4054-9B88-9041FEC98037@microsoft.com...
> Hi All,
>
> Am using the following XML String. I want to get all the values from the
> XML. but i dont know how to get it. Can any one please help me ASAP.
> Eg:
> '<?xml version="1.0" ?>
> <ArrayOfAdminSearchCriteria>
> <AdminSearchCriteria Name="Test0">
> <Value>0</Value>
> <Value>T0</Value>
> </AdminSearchCriteria>
> <AdminSearchCriteria Name="Test1">
> <Value>1</Value>
> <Value>T1</Value>
> </AdminSearchCriteria>
> <AdminSearchCriteria Name="Test2">
> <Value>2</Value>
> <Value>T2</Value>
> </AdminSearchCriteria>
> </ArrayOfAdminSearchCriteria>'
>
> Am using the following Method
> SELECT
> MetadataName,
> Value
> FROM OPENXML (@idoc,
> '/ArrayOfAdminSearchCriteria/AdminSearchCriteria/Value',1)
> WITH
> (
> MetadataName NVARCHAR(50) '@Name',
> Value NVARCHAR(255)
>
> )XMLData
>
> Output i want
> ---------------
> Test0 0
> Test0 T0
> Test1 1
> Test1 T1
> Test2 2
> Test2 T2
>
>
> Thanks in advance..
>
> Regards,
> Herbert

"SriSamp" wrote:

> Does this suffice?
> =====
> DECLARE @xml NVARCHAR(4000)
> DECLARE @handle INT
> SET @xml = N'
> <?xml version="1.0" ?>
> <ArrayOfAdminSearchCriteria>
> <AdminSearchCriteria Name="Test0">
> <Value>0</Value>
> <Value>T0</Value>
> </AdminSearchCriteria>
> <AdminSearchCriteria Name="Test1">
> <Value>1</Value>
> <Value>T1</Value>
> </AdminSearchCriteria>
> <AdminSearchCriteria Name="Test2">
> <Value>2</Value>
> <Value>T2</Value>
> </AdminSearchCriteria>
> </ArrayOfAdminSearchCriteria>'
> EXEC sp_xml_preparedocument @handle OUTPUT, @xml
> SELECT
> MetadataName, Value
> FROM OPENXML (@handle,
> '/ArrayOfAdminSearchCriteria/AdminSearchCriteria/Value', 2)
> WITH
> (
> MetadataName NVARCHAR(50) '../@Name',
> Value NVARCHAR(255) '.'
> ) AS XMLData
> EXEC sp_xml_removedocument @handle
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
>
> "Herbert" <Herbert@discussions.microsoft.com> wrote in message
> news:A796F68B-DCB5-4054-9B88-9041FEC98037@microsoft.com...
> > Hi All,
> >
> > Am using the following XML String. I want to get all the values from the
> > XML. but i dont know how to get it. Can any one please help me ASAP.
> > Eg:
> > '<?xml version="1.0" ?>
> > <ArrayOfAdminSearchCriteria>
> > <AdminSearchCriteria Name="Test0">
> > <Value>0</Value>
> > <Value>T0</Value>
> > </AdminSearchCriteria>
> > <AdminSearchCriteria Name="Test1">
> > <Value>1</Value>
> > <Value>T1</Value>
> > </AdminSearchCriteria>
> > <AdminSearchCriteria Name="Test2">
> > <Value>2</Value>
> > <Value>T2</Value>
> > </AdminSearchCriteria>
> > </ArrayOfAdminSearchCriteria>'
> >
> > Am using the following Method
> > SELECT
> > MetadataName,
> > Value
> > FROM OPENXML (@idoc,
> > '/ArrayOfAdminSearchCriteria/AdminSearchCriteria/Value',1)
> > WITH
> > (
> > MetadataName NVARCHAR(50) '@Name',
> > Value NVARCHAR(255)
> >
> > )XMLData
> >
> > Output i want
> > ---------------
> > Test0 0
> > Test0 T0
> > Test1 1
> > Test1 T1
> > Test2 2
> > Test2 T2
> >
> >
> > Thanks in advance..
> >
> > Regards,
> > Herbert
>
>