在xml列中获取节点值

Please let me know why the following XML query is not fetching any result. I am trying to get value EffectiveUserName tag.

请让我知道为什么下面的XML查询没有获取任何结果。我正在尝试获取有效的用户名标签。

DECLARE @MyXML XML
        SET @MyXML = '<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
      <Catalog>name</Catalog>
      <Timeout>600</Timeout>
      <Format>Native</Format>
      <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
      <Cube>Model</Cube>
      <DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse>
      <DbpropMsmdActivityID>68A6900B-20F8-4A02-AEC3-7C56B2D3C5D5</DbpropMsmdActivityID>
      <DbpropMsmdRequestID>A0D1E07F-AE29-4CCA-AEE4-3B79D97CA426</DbpropMsmdRequestID>
      <DbpropMsmdCurrentActivityID>68A6900B-20F8-4A02-AEC3-7C56B2D3C5D5</DbpropMsmdCurrentActivityID>
      <LocaleIdentifier>1033</LocaleIdentifier>
      <EffectiveUserName>userid@domainname.com</EffectiveUserName>
      <sspropinitappname>PowerBI</sspropinitappname>
    </PropertyList>'


    select  @MyXML.value('(/PropertyList/EffectiveUserName)[1]','varchar(max)')

2 个解决方案

#1

Your XML has a default namespace that you must respect and include in your query!

您的XML有一个默认的名称空间，您必须尊重它并在查询中包含它!

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"  
              ***********************************************
              xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

Use this code to grab the value you're looking for by defining the default XML namespace for your query:

通过定义查询的默认XML名称空间，使用此代码获取要查找的值:

;WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:xml-analysis')
SELECT
    @MyXML.value('(/PropertyList/EffectiveUserName)[1]', 'varchar(50)')

#2

You can ignore the namespace by using *: before the tag names:

可以使用*:在标记名之前忽略名称空间:

select  @MyXML.value('(/*:PropertyList/*:EffectiveUserName)[1]','varchar(max)')

#1