August 11, 2017 at 4:37 pm
I've been trying to figure out the syntax for pulling the value for INSTANCENAME. Here is the top of the XML that resides in the row:
<DataFeed xmlns="http://www.archer-tech.com/datafeed/dfx/2010/04" xmlns:plugin="pluginExtensions" Type="TODO" Guid="TODO" UserAccount="userArcherDataFeedService" Locale="en-US" DateFormat="" ThousandSeparator="" NegativeSymbol="" DecimalSymbol="" SendingNotifications="false" SendJobStatusNotifications="false" RecipientUserIds="" RecipientGroupIds="" RecipientEmailAddresses="" Name="Copy Review Workpaper - Update Business Unit Level 2" Description="" Active="true">
<Tokens>
<Token name="DataFileDirectoryName" />
<Token name="DataFileName" />
<Token name="DataFileExtension" />
<Token name="LastRunTime">2017-08-11T05:08:08Z</Token>
<Token name="LastFileProcessed" />
</Tokens>
<Transporter>
<transporters:ArcherWebServiceTransportActivity xmlns:transporters="clr-namespace:ArcherTech.DataFeed.Activities.Transporters;assembly=ArcherTech.DataFeed" xmlns:out="clr-namespace:ArcherTech.DataFeed;assembly=ArcherTech.DataFeed" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:compModel="clr-namespace:ArcherTech.DataFeed.ComponentModel;assembly=ArcherTech.DataFeed" xmlns:channel="clr-namespace:ArcherTech.DataFeed.Engine.Channel;assembly=ArcherTech.DataFeed" xmlns:engine="clr-namespace:ArcherTech.DataFeed.Engine;assembly=ArcherTech.DataFeed" xmlns:kernel="clr-namespace:ArcherTech.Kernel.Channel;assembly=ArcherTech.Kernel" xmlns="clr-namespace:ArcherTech.DataFeed;assembly=ArcherTech.DataFeed" xmlns:schema="clr-namespace:System.Xml.Schema;assembly=System.Xml" xmlns:xmlLinq="clr-namespace:System.Xml.Linq;assembly=System.Xml" xmlns:domain="clr-namespace:ArcherTech.Common.Domain;assembly=ArcherTech.Common" xmlns:s="clr-namespace:System;assembly=mscorlib" x:Key="transportActivity" SearchType="ReportId" Uri="[server URL]" RecordsPerFile="100" ReportID="**" UseWindowsAuth="false" IsWindowsAuthSpecific="false" WindowsAuthUserName="**==" WindowsAuthPassword="**==" WindowsAuthDomain="" ProxyName="" ProxyPort="8080" ProxyUsername="" ProxyPassword="" ProxyDomain="" IsProxyActive="False" ProxyOption="None" InstanceName="This Value Here" TempFileOnSuccessAction="DoNothing" TempFileOnSuccessRenameString="" TempFileOnErrorAction="DoNothing" TempFileOnErrorRenameString="" Transform="{engine:DataFeedBinding Path=Transform}" SessionContext="{engine:DataFeedBinding Path=Session}">
<transporters:ArcherWebServiceTransportActivity.Credentials>
</transporters:ArcherWebServiceTransportActivity.Credentials>
</transporters:ArcherWebServiceTransportActivity>
</Transporter>
Query:
;WITH XMLNAMESPACES
('http://www.archer-tech.com/datafeed/dfx/2010/04' AS ns)
SELECT configuration_xml
,configuration_xml.value('(/ns:DataFeed/ns:Transport/*:x/ns:InstanceName)[1]', 'varchar(50)')
FROM @varDataFeed VDF
WHERE TDF.configuration_xml.exist('(/ns:DataFeed/ns:Transporter)') = 1
I realize the line is incorrect, I've been trying to figure out how to get to the InstanceName value. The configuration_xml was being selected just to see if my WHERE clause was correct. The namespace reference is correct, it's used in another SELECT statement that does work, but the value is not buried so far in the XML. Any help is very appreciated!
August 14, 2017 at 10:47 am
This "value" worked for me:
.value('((/*:DataFeed/*:Transporter[1]/*:ArcherWebServiceTransportActivity[1])/@InstanceName)[1]', 'varchar(50)')
Your node names need to match (you had Transport instead of Transporter, etc...) or you won't get anywhere. Also - InstanceName is an attribute of the ArcherWebServiceTransportActivity, so you need to prefix it with @
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 14, 2017 at 11:00 am
Hey thanks!!! Your help is much appreciated 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply