title: Extract xml from another xml using OpenXML
body: <p>I have an xml, for example:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;AP&gt;
    &lt;value UserXML="&lt;ADUsers&gt;&lt;ADUser DisplayName = "User3 (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR" /&gt;&lt;/ADUsers&gt;" SIDHash="ad8b3d173afa97b49b98c57b182356b6d0066f8c" Type="USER" DisplayName="User3 (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR" /&gt; 
    &lt;value UserXML="&lt;ADUsers&gt;&lt;ADUser DisplayName = "User4 (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR" /&gt;&lt;/ADUsers&gt;" SIDHash="7a0600b592180dba051ec99f8b8b960515d3e05f" Type="USER" DisplayName="User4 (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR" /&gt; 
  &lt;/AP&gt;
  </code></pre>
  
  <p>I have an xml (ADUsers inside my main xml AP)</p>
  
  <p>I want to insert the ADUsers xml into a column in my sql database:</p>
  
  <p>here is my query:</p>
  
  <pre><code>...
      DECLARE @InsertedAP AS TABLE (ID INT);
  
  
      DECLARE @docHandle INT
      EXEC sp_xml_preparedocument @docHandle OUTPUT, @AP_List
  
      INSERT INTO
          AssociationPoints
          (APType,UserXML,SIDHash)
      OUTPUT INSERTED.AssociationPointID INTO @InsertedAP
  
      SELECT
          AP.[Type],
          CAST(AP.UserXML as XML),
          AP.SIDHash
      FROM
          OPENXML(@docHandle, 'AP/value', 1)
          WITH
              ([Type] CHAR(4), UserXML XML, SIDHash VARCHAR(50)) AP
  
      IF @@ERROR &lt;&gt; 0
      BEGIN
          ROLLBACK
          RETURN
      END
  
  ...
  </code></pre>
  
  <p>but I get the error:
  Element-centric mapping must be used with OPENXML when one of the columns is of type XML.</p>
  
  <p>why?
  how can I solve this issue?
  (The inner xml is sent encoded to the DB)</p>
  
  <p>I attach script for example that can be run:</p>
  
  <pre><code>    declare @AP_List XML
      set @AP_List = '&lt;AP&gt;&lt;value UserXML="&amp;lt;ADUsers&amp;gt;&amp;lt;ADUser DisplayName = &amp;quot;User3   (rdserver\TEST\user3)&amp;quot; Username=&amp;quot;TEST\user3&amp;quot; DomainID=&amp;quot;43&amp;quot; UserSID=&amp;quot;ad8b3d173afa97b49b98c57b182356b6d0066f8c&amp;quot; UserTypeCode=&amp;quot;INTR&amp;quot; /&amp;gt;&amp;lt;/ADUsers&amp;gt;" SIDHash="ad8b3d173afa97b49b98c57b182356b6d0066f8c" Type="USER" DisplayName="User3   (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR"&gt;&lt;/value&gt;&lt;value UserXML="&amp;lt;ADUsers&amp;gt;&amp;lt;ADUser DisplayName = &amp;quot;User4   (rdserver\TEST\user4)&amp;quot; Username=&amp;quot;TEST\user4&amp;quot; DomainID=&amp;quot;43&amp;quot; UserSID=&amp;quot;7a0600b592180dba051ec99f8b8b960515d3e05f&amp;quot; UserTypeCode=&amp;quot;INTR&amp;quot; /&amp;gt;&amp;lt;/ADUsers&amp;gt;" SIDHash="7a0600b592180dba051ec99f8b8b960515d3e05f" Type="USER" DisplayName="User4   (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR"&gt;&lt;/value&gt;&lt;/AP&gt;'
  
  
  
  
          DECLARE @docHandle INT
  EXEC sp_xml_preparedocument @docHandle OUTPUT, @AP_List
  
  SELECT
      AP.[Type],
      CAST(AP.UserXML as XML),
      AP.SIDHash
  FROM
      OPENXML(@docHandle, 'AP/value', 1)
      WITH
          ([Type] CHAR(4), UserXML XML, SIDHash VARCHAR(50)) AP
  </code></pre>