title: SELECT * FROM OPENXML IN STORED PROCEDURE returns empty row
body: <p>I have a problem using openxml function in my stored procedure, as you can see, the select statement <strong>returns empty row</strong>. I suspect the sql couldn't read the xml directories.this is the first time i'm using this function, so i am not sure is this the right way to use it.btw i am using sql server 2008 r2. here's the code to reproduce the problem.</p>
  
  <pre><code>Declare @hddoc int
  Declare @Doc xml 
  
  SET @Doc = ' &lt;DataSet&gt;
       &lt;tblEmp&gt;
        &lt;name&gt;Nazri&lt;/name&gt; 
        &lt;designation&gt;Developer&lt;/designation&gt;  
      &lt;/tblEmp&gt;
      &lt;tblEmp&gt;
        &lt;name&gt;Jibin&lt;/name&gt; 
   &lt;designation&gt;System Analyst&lt;/designation&gt; 
     &lt;/tblEmp&gt;
            &lt;/DataSet&gt;'
  
  EXEC SP_XML_PREPAREDOCUMENT @hddoc OUTPUT, @Doc
  
  SELECT @hddoc
  
  DECLARE @iDoc INT
  SET @iDoc = 1
  
  SELECT *
  FROM OPENXML(@iDoc,'/Dataset/tblEmp')
     WITH ( name varchar(50) '@name',
     designation varchar(50) '@designation'
      )
  </code></pre>