title: Transform XML to Table data using XQuery in SQL Server
body: <p>Is it possible to achieve the following table from of output using XQuery in SQL Server</p>
  
  <p>Edit: A change in my requirement, Consider i have a table which stores the below xml and also UserID</p>
  
  <pre><code>DECLARE  @XML xml
  set @XML = '&lt;Security&gt;
               &lt;FiscalYear Name="2012"&gt;
              &lt;Country Id="204"&gt;
                &lt;State Id="1"&gt;
                  &lt;City Id="10"&gt;&lt;/City&gt;
                &lt;/State&gt;
                &lt;State Id="2"&gt;
                  &lt;City Id="20"&gt;&lt;/City&gt;
                  &lt;City Id="30"&gt;&lt;/City&gt;
                &lt;/State&gt;
                &lt;State Id ="3"&gt;&lt;/State&gt;
                &lt;/Country &gt;
              &lt;/FiscalYear&gt;
          &lt;/Security&gt;'
  
  CREATE TABLE #tmp_user
  (UserID INT,SecurityXML XML)
  
  INSERT INTO #tmp_user
          ( UserID, SecurityXML )
  VALUES  ( 1, 
            @XML
            )
  </code></pre>
  
  <p>Now how can i get a o/p like</p>
  
  <p>Output:  </p>
  
  <pre><code> UserID StateID       CityID
       1      1           10
       1      2           20
       1      2           30
       1      3            0
  </code></pre>
  
  <p>Is it possible to achieve?       </p>