从XML进行SQL查询

Question

我正在尝试查询一个XML文件，以提取用户/许可证信息。不过，我似乎被卡住了。下面是我的数据集：

<?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>'

我试图实现的是一个SQL SELECT，它像这样返回数据：

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

但这就是我不断得到的结果：

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

我不知道如何从不同的节点返回UserName，任何帮助都会很好！下面是我的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 )

Answer 1

您需要像这样更改查询的select部分。

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 )

Answer 2

用户是比模块更高的嵌套级别。

因此，您可以从XML中获取用户节点。然后交叉应用其中的模块。

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