"Herbert" <Herb***@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: sris***@gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
>
> "Herbert" <Herb***@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
>
>
>