CODEWITHSUNDEEP
sqlsql-serverxml
JPC12
JPC12

Reputation: 25

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:

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 )

Upvotes: 1

Views: 65

Answers (2)

LukStorms
LukStorms

Reputation: 29677

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]);

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13403

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 )

Upvotes: 2

Related Questions