title: XML Parse in T-SQL
body: <p>I am trying to parse XML document inside of T-SQL. XML Document look like when there is some clients:</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt; 
  &lt;ArrayOfServerSettings&gt;
     &lt;ServerSettings&gt;
       &lt;Address&gt;10.0.1.1&lt;/Address&gt;
       &lt;Nodes&gt;
          &lt;NodeSettings&gt;
              &lt;PortNumber&gt;5000&lt;/PortNumber&gt;
              &lt;ClientIds&gt;
                  &lt;int&gt;1&lt;/int&gt;
                  &lt;int&gt;2&lt;/int&gt;
                  &lt;int&gt;3&lt;/int&gt;
              &lt;/ClientIds&gt;
          &lt;/NodeSettings&gt;
        &lt;/Nodes&gt;
     &lt;/ServerSettings&gt;
  &lt;/ArrayOfServerSettings&gt;
  </code></pre>
  <p>if there is no any clients, it looks like</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt; 
  &lt;ArrayOfServerSettings&gt;
     &lt;ServerSettings&gt;
       &lt;Address&gt;10.0.1.1&lt;/Address&gt;
       &lt;Nodes&gt;
          &lt;NodeSettings&gt;
              &lt;PortNumber&gt;5000&lt;/PortNumber&gt;
              &lt;ClientIds /&gt;
          &lt;/NodeSettings&gt;
        &lt;/Nodes&gt;
     &lt;/ServerSettings&gt;
  &lt;/ArrayOfServerSettings&gt;
  </code></pre>
  <p>In the first case, the table should look like</p>
  <pre><code>Address     PortNumber     ClientId
  10.0.1.1      5000           1
  10.0.1.1      5000           2
  10.0.1.1      5000           3
  </code></pre>
  <p>When there is no client I should parse them like</p>
  <pre><code>Address     PortNumber     ClientId
  10.0.1.1      5000           0
  </code></pre>
  <p>Here is my t-sql statements</p>
  <pre><code>declare @xml XML
  set @xml = N'&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt;
  &lt;ArrayOfCalculationServerSettings&gt;
     &lt;CalculationServerSettings&gt;
        &lt;Address&gt;10.0.1.1&lt;/Address&gt;
        &lt;Nodes&gt;
           &lt;NodeSettings&gt;
              &lt;PortNumber&gt;10009&lt;/PortNumber&gt;
              &lt;ClientIds&gt;
                 &lt;int&gt;1&lt;/int&gt;
                 &lt;int&gt;2&lt;/int&gt;
                 &lt;int&gt;3&lt;/int&gt;
              &lt;/ClientIds&gt;
           &lt;/NodeSettings&gt;
        &lt;/Nodes&gt;
    &lt;/CalculationServerSettings&gt;
  </code></pre>
  <p>'</p>
  <pre><code>DECLARE @DocID INT 
  EXEC sp_xml_preparedocument @DocID OUTPUT, @xml
  
  SELECT *   
  FROM
      OPENXML(@DocID, '/ArrayOfServerSettings/ServerSettings/Nodes/NodeSettings/ClientIds/int', 1)
      WITH (
          Address NVARCHAR(256) '../../../../Address[1]',
          PortNumber    INT           '../../PortNumber[1]',
          ClientID      INT           '.'
      ) AS D0
  
  EXEC sp_xml_removedocument @DocID
  </code></pre>
  <p><strong>But my approach is not working for the second option</strong></p>