Can anyone help me?.I am running this script but it does not give any result.

declare @doc varchar(8000)
select @doc ='
<?xml version="1.0" encoding="utf-8" ?>
<UTSv_ProviderAvailRS xmlns="http://www.eviivo.com/UTSv/2004/01/01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.eviivo.com/UTSv/2004/01/01/UTSv_ProviderAvailRS.xsd" TimeStamp="2004-08-17T09:30:47-05:00" Target="Production">
<ProductProviders>
  <ProductProvider ID="DMS_PROV_ID_1" SpecialOffer="true" />
  <ProductProvider ID="DMS_PROV_ID_2" Package="true" />
  </ProductProviders>
  </UTSv_ProviderAvailRS>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/UTSv_ProviderAvailRS/ProductProviders/ProductProvider',2)
         WITH (ID varchar(30) '@ID'
          ,SpecialOffer varchar(30) '@SpecialOffer'
              ,Package varchar(30) '@Package'
             )

I suggest you read up on namespaces:

http://www.dpawson.co.uk/xsl/sect2/N5536.html

especially numbers 13 and 23.

Then take a look at this:

http://www.sqlxml.org/faqs.aspx?faq=66

it shows how to declare a namespace prefix mapping with OPENXML.



--

Joe (Microsoft MVP - XML)