SQL Querying From XML

I’m trying to query an XML file, to extract user/license information. I seem to be stuck though. Below is my data set:

<?xml version="1.0" encoding="UTF-16"?>
<Users>
  <User>
<UserName>Elise</UserName>
<IsConnected>1</IsConnected>
    <Modules>
      <Module>
<KeyType>LOGISTICS-LTD</KeyType>
<KeyDesc>Limited Logistics User</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
      <Module>
<KeyType>SAP-ADDONS</KeyType>
<KeyDesc>SAP AddOns</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
      <Module>
<KeyType>SAP0000007050</KeyType>
<KeyDesc>Workflow</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
    </Modules>
  </User>
  <User>
<UserName>alice</UserName>
<IsConnected>0</IsConnected>
    <Modules>
      <Module>
<KeyType>BASIS0001000061</KeyType>
<KeyDesc>Magma Credit Cards</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
      <Module>
<KeyType>LOGISTICS-LTD</KeyType>
<KeyDesc>SAP Business One Limited Logistics User</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
      <Module>
<KeyType>SAP-ADDONS</KeyType>
<KeyDesc>SAP AddOns</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
      <Module>
<KeyType>SAP0000007050</KeyType>
<KeyDesc>Workflow</KeyDesc>
<DbType>MSS</DbType>
<BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
<ReferingCount>0</ReferingCount>
<InstallNo>0020534918</InstallNo>
      </Module>
    </Modules>
  </User>
</UserS>'

What i’m trying to achieve is a SQL SELECT that returns the data like this:

  • SQL Table partitioning
  • Query a database based on result of query from another database
  • Explicit lock to table(s) in SQL Server?
  • Grouping data in SQL Server for particular patterns
  • Date Range for set of same data
  • How are varchar values stored in a SQL Server database?
  • UserName    KeyType KeyDesc
    Elise   LOGISTICS   Limited Logistics
    Elise   SAP-ADDONS  SAP AddOns
    Elise   SAP0000007050   Workflow
    Alice   BASIS0001000061 Magma Credit Card
    Alice   LOGISTICS   Limited Logistics
    Alice   SAP-ADDONS  SAP AddOns
    Alice   SAP0000007050   Workflow
    

    But this is what I keep getting returned:

    UserName    KeyType KeyDesc
    NULL    LOGISTICS   Limited Logistics
    NULL    SAP-ADDONS  SAP AddOns
    NULL    SAP0000007050   Workflow
    NULL    BASIS0001000061 Magma Credit Card
    NULL    LOGISTICS   Limited Logistics
    NULL    SAP-ADDONS  SAP AddOns
    NULL    SAP0000007050   Workflow
    

    I cannot work out how to return the UserName from a different node, any helps would be great!!! Below is my SQL:

    DECLARE @xmlData XML
    set @xmlData='<?xml version="1.0"?>
    <Users>
      <User>
    <UserName>Elise</UserName>
    <IsConnected>1</IsConnected>
        <Modules>
          <Module>
    <KeyType>LOGISTICS-LTD</KeyType>
    <KeyDesc>Limited Logistics User</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
          <Module>
    <KeyType>SAP-ADDONS</KeyType>
    <KeyDesc>SAP AddOns</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
          <Module>
    <KeyType>SAP0000007050</KeyType>
    <KeyDesc>Workflow</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
        </Modules>
      </User>
      <User>
    <UserName>alice</UserName>
    <IsConnected>0</IsConnected>
        <Modules>
          <Module>
    <KeyType>BASIS0001000061</KeyType>
    <KeyDesc>Magma Credit Cards</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
          <Module>
    <KeyType>LOGISTICS-LTD</KeyType>
    <KeyDesc>SAP Business One Limited Logistics User</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
          <Module>
    <KeyType>SAP-ADDONS</KeyType>
    <KeyDesc>SAP AddOns</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
          <Module>
    <KeyType>SAP0000007050</KeyType>
    <KeyDesc>Workflow</KeyDesc>
    <DbType>MSS</DbType>
    <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>
    <ReferingCount>0</ReferingCount>
    <InstallNo>0020534918</InstallNo>
          </Module>
        </Modules>
      </User>
    </Users>'
    
    
    SELECT 
      ref.value('UserName[1]', 'NVARCHAR(100)') AS 'User' ,
      ref.value('KeyType[1]', 'NVARCHAR (100)') AS 'Type' ,
      ref.value('KeyDesc[1]', 'NVARCHAR (100)') AS 'Desc'      
    FROM @xmlData.nodes('/Users/User/Modules/Module') 
    xmlData( ref )
    

    2 Solutions collect form web for “SQL Querying From XML”

    You need to change select part of the query like this.

    SELECT 
      ref.value('../../UserName[1]', 'NVARCHAR(100)') AS 'User' ,
      ref.value('KeyType[1]', 'NVARCHAR (100)') AS 'Type' ,
      ref.value('KeyDesc[1]', 'NVARCHAR (100)') AS 'Desc'      
      FROM @xmlData.nodes('/Users/User/Modules/Module') 
    xmlData( ref )
    

    The User is a nesting level higher than the Modules.

    So you could get the User nodes from the XML.
    And then cross apply the Modules in them.

    SELECT 
      [User].value('UserName[1]', 'NVARCHAR(100)') AS 'User' ,
      [Module].value('KeyType[1]', 'NVARCHAR(100)') AS 'Type' ,
      [Module].value('KeyDesc[1]', 'NVARCHAR(100)') AS 'Desc'      
    FROM @xmlData.nodes('Users/User') Users([User])
    CROSS APPLY [User].nodes('Modules/Module') Modules([Module]);
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.