October 28, 2016 at 5:40 am
Hi folks, this is probably a simple one for someone. I've confused myself over (i'm a beginner with XML queries) it and cannot get it to return the value I want.
I have a XML column "RequestProperties" in a temp table #Data and this has the below in some of the rows.
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<Catalog>My_CAT</Catalog>
<Cube>My_Cube</Cube>
<DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse>
<DbpropMsmdActivityID>487B494A-5CCF-443D-8D34-14826683F6EF</DbpropMsmdActivityID>
<DbpropMsmdRequestID>FDC58218-0853-4611-8DD1-BC6AD22C5CB2</DbpropMsmdRequestID>
<LocaleIdentifier>2057</LocaleIdentifier>
<EffectiveUserName>user.name@mydomain.co.uk</EffectiveUserName>
<sspropinitappname>APP</sspropinitappname>
</PropertyList>
How do I pull out the <EffectiveUserName> value
Tried a few things:
SELECT m.c.value('(EffectiveUserName)[1]','varchar(100)') AS EffectiveUsername
FROM #Data
CROSS APPLY RequestProperties.nodes('PropertyList') m(c)
Returns nothing.
SELECT RequestProperties.value(N'(PropertyList/EffectiveUserName)[1]', N'varchar(100)') AS EffectiveUserName
FROM #Data
Returns NULL.
Any help would be appreciated.
Cheers
Rod
October 28, 2016 at 6:00 am
Quick suggestion, use wildcard for the namespaces
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#Data') IS NOT NULL DROP TABLE #Data;
SELECT
CONVERT(XML,'<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<Catalog>My_CAT</Catalog>
<Cube>My_Cube</Cube>
<DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse>
<DbpropMsmdActivityID>487B494A-5CCF-443D-8D34-14826683F6EF</DbpropMsmdActivityID>
<DbpropMsmdRequestID>FDC58218-0853-4611-8DD1-BC6AD22C5CB2</DbpropMsmdRequestID>
<LocaleIdentifier>2057</LocaleIdentifier>
<EffectiveUserName>user.name@mydomain.co.uk</EffectiveUserName>
<sspropinitappname>APP</sspropinitappname>
</PropertyList>',0) AS RequestProperties
INTO #Data;
SELECT
D.RequestProperties.value('(*:PropertyList/*:EffectiveUserName/text())[1]','NVARCHAR(100)') AS EffectiveUserName
FROM #Data D;
Output
EffectiveUserName
-------------------------
October 30, 2016 at 3:37 pm
Awesome, i'll give it a try. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply