Getting node value in xml column

Question

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

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>[email protected]</EffectiveUserName>
      <sspropinitappname>PowerBI</sspropinitappname>
    </PropertyList>'


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

marc_s · Accepted Answer · 2017-01-13 18:03:45Z

3

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

<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:

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

answered Jan 13, 2017 at 18:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shiva Kumar Over a year ago

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

Gottfried Lesigang Over a year ago

@ShivaKumar you obviously followed Aducci's advice to use a wildcard for the namespace. That's OK, but you should be aware, that being as specific as possilbe is better. And please allow me one hint: If one of the answers solves your problem, please tick the acceptance check below the answer's vote counter. This will 1) mark this issue as solved 2) make it easier to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Cheers

Aducci · Accepted Answer · 2017-01-13 18:06:04Z

3

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

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

answered Jan 13, 2017 at 18:06

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

Although this works it is something one should not suggest as general hint. In general with XML it is best (fastest and most secure), to be as specific as possible...

Collectives™ on Stack Overflow

Getting node value in xml column

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related