Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Saturday, October 17, 2009 3:19 PM | 1 vote
Hi all,
I'm hoping this is a really simple one. I'm executing the following:
declare @x xml = '<Property Name="SomePropertyName">3</Property>';
select @x.query('@Name');
and its giving me the error you see in the subject of this thread. Please can someone
- Tell me what the rror means and why I'm getting it
- (More importantly) Tell me how to fix it.
Oh, FYI, I'm simply trying to extract the value of the "Name" attribute (i.e. 'SomePropertyName')
Thanks in advance all!
JamieT
http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
Saturday, October 17, 2009 9:39 PM ✅Answered | 3 votes
Hi, Jamie.:
There's actually several different things going on here, first, AFAIK, an XQuery of '@Name' means {"return all of the nodes and their children that are attributes, called 'Name', and that are at the top"} (because there's no child or descent operators like '/' or '//'). So the error is just saying that Attributes cannot be at the top, only Elements can be at the top (because Attributes are basically like scalar parameter values). To fix this you need syntax like ("ElementName/@AttributeName").
Secondly, most of the XML methods require single values or XML nodes to be returned (".nodes()" is the exception) and unlike SQL, Xquery is not willing to wait until execution to see if this works out, you must supply an Xquery that syntactically, can *only* return a single value. Thus ("Property/@Name") will not work, because it *might* find more than one "<Property>" element. The usual way to fix this is to include an occurrence ordinal predicate ("[..]") value, typically "1" to indicate the first occurrence of the preceding Xquery spec. So, ("Property[1]/@Name") would tell it to return the first top-level Property element's Name attribute. You do not have to throw another "[1]" after @Name only because Attributes have to be unique by name within their parent Elements.
Finally, the XML.query() method is going to try to return an XML data-type and a scalar value like "SomePropertyName" isn't XML, however you can coax it into converting it into an Element (which is XML) with the Xquery string() function. Alternatively, you can use the XML.value() function which normally does return scalar values. Here's an example of both that should work for you:
declare @x xml = '<Property Name="SomePropertyName">3</Property>';
select @x.query('string(Property[1]/@Name)');
select @x.value('Property[1]/@Name','varchar(32)');
Hope this helps.
(ps., Love your blog. It was a huge help to me in learning and starting up on SSIS.)
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung*
Proactive Performance Solutions, Inc.* "Performance is our middle name."
Please! Remember to Vote all helpful replies as Helpful
Saturday, October 17, 2009 11:52 PM ✅Answered
The XML.nodes() function returns a rowset of pointers into the XML structure, that point to each matching instance of the Xquery. In your case that searxht is "/DTS:Executable/DTS:Property", which means that for each returned row, the "current pointer" or "." is pointing to each instance of "/DTS:Executable/DTS:Property". However, this also means that you need to replace the previous references to "Property" with a reference to the current pointer (".") instead.
This works for me:
SELECT Pkg.props.query('.') as property
, Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Name','varchar(32)') as name
, Pkg.props.value('.', 'nvarchar(max)') as value
FROM (
select cast(pkgblob.BulkColumn as XML) pkgXML
from openrowset(bulk 'C:\byoung\SQL Server\SSIS\TemplatePackage\TemplatePackage.dtsx',single_blob) as pkgblob
) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Pkg(props)
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung*
Proactive Performance Solutions, Inc.* "Performance is our middle name."
Please! Remember to Vote all helpful replies as Helpful
Saturday, October 17, 2009 9:59 PM | 1 vote
Correction: the reason why x.query('Property[1]./@Name') does not work is because it returns an Attribute, which cannot be XML on its own. The Xquery string() or data() function fix this because they extract the Attribute's value only, which is returned as a string that x.query() then implicitly converts into a XML "Data".
(I think that I have this right, now. I basically had it backwards before.)
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung*
Proactive Performance Solutions, Inc.* "Performance is our middle name."
Please! Remember to Vote all helpful replies as Helpful
Saturday, October 17, 2009 10:11 PM
Barry,
Thankyou SO much, you've really helped me out of a hole here. Look for something along these lines turning up on the new blog in the next few days.
I admit I don't yet fully understand WHY it works but to be honest I don't really care :)
Oh, and thank you for the kind words about the blog too - nice to know it is appreciated.
cheers
Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
Saturday, October 17, 2009 10:19 PM
Stil there Barry, fancy another brain teaser? :)
The ultimate aim here is to query the contents of a .dtsx file using xquery. What I want to do is get all the name-value pair properties of the package. Your code above works but for some reason when I plug it into a bigger query it doesn't and I can't work out why.
Run the following query:
SELECT Pkg.props.query('.') as property
, Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";p1:Property[1]/@p1:Name','varchar(32)') as name
, Pkg.props.value('.', 'nvarchar(max)') as value
FROM (
select cast(pkgblob.BulkColumn as XML) pkgXML
from openrowset(bulk 'C:\tmp\MyPkg.dtsx',single_blob) as pkgblob
) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Pkg(props)
(replacing 'C:\tmp\MyPkg.dtsx' with a package on your hard drive.)
For some reason it doesn't pull out the name of the property and I cannot for the life of me work out why. Any help is much appreciated.
-Jamie
http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
Saturday, October 17, 2009 11:05 PM
Barry,
...
I admit I don't yet fully understand WHY it works but to be honest I don't really care :)
...
Jamie
http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
Honestly, I've been using it intermittently for over a year now, and I still feel like that most of the time. :-)
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung*
Proactive Performance Solutions, Inc.* "Performance is our middle name."
Please! Remember to Vote all helpful replies as Helpful
Sunday, October 18, 2009 6:21 AM
GENIUS! Thanks Barry!
http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
Thursday, August 29, 2013 9:17 PM
Hi Barry,
I'm not sure if you are still checking this thread (I sure hope so). But I have been tasked to go through all of our SSIS packages and retreive the Oracle server/table names of all our source loads (about 500 packages).
I'm trying to use XQuery to return the DTS:ObjectData components/properties/property name="SqlCommand" which would the query which would give me the schema/table. I would also need the connection manager information so I can grab the Oracle server.
Is this possible or am I living a dream. I've played with yours and Jamie's XQueries and made little progress. Any help would be greatly appreciated.
Thank you,
Brian
Friday, August 30, 2013 2:21 PM | 1 vote
What version of SQL Server are you using? (As it's different depending on that).
Also are your files stored in msdb or on the filesystem? Are you using .dtsConfig files? If so, the connection strings in the package will be overrridden at runtime.
Try this which should show you how to read the connection strings and source table / SQL for all packages in msdb.
USE msdb
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
-- Trap the packagedata into a table and cast to xml
SELECT id, name, verbuild, CAST(CAST( packagedata AS VARBINARY(MAX) ) AS XML) AS packagedata
INTO #tmp
--FROM msdb..sysdtspackages90 WITH(NOLOCK) -- SQL 2005
FROM msdb..sysssispackages WITH(NOLOCK) -- SQL 2008 R2
WHERE description != 'System Data Collector Package'
GO
-- Read connection strings
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS )
SELECT
'OLEDB' s,
name,
o.c.value('.', 'varchar(max)') connectionManagerName,
cs.c.value('.', 'varchar(max)') connectionString
FROM #tmp packageXML
CROSS APPLY packageXML.packagedata.nodes('//DTS:ConnectionManager') cm(c)
CROSS APPLY cm.c.nodes('DTS:Property[@DTS:Name="ObjectName"]') o(c)
CROSS APPLY cm.c.nodes('DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]') cs(c)
UNION ALL
SELECT
'Attunity' s,
name,
o.c.value('(OraConnectionString/text())[1]', 'varchar(max)') connectionManagerName,
o.c.value('.', 'varchar(max)') connectionString
FROM #tmp packageXML
CROSS APPLY packageXML.packagedata.nodes('//DTS:ObjectData') cm(c)
CROSS APPLY cm.c.nodes('OracleXMLPackage') o(c)
-- Read Oracle datasources
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS )
SELECT
name,
o.c.value('@name', 'varchar(max)') componentName,
cs.c.value('@name', 'varchar(max)') propertyName,
cs.c.value('.', 'varchar(max)') propertyValue
FROM #tmp packageXML
CROSS APPLY packageXML.packagedata.nodes('//DTS:ObjectData') cm(c)
CROSS APPLY cm.c.nodes('pipeline/components/component') o(c)
OUTER APPLY o.c.nodes('properties/property[(@name="TableName" or @name="SqlCommand" or @name="OpenRowset")]') cs(c)
You may need to tweak it slightly for your requirements / packages.
Are you using the Attunity connectors? If not you should be. They are free, compatible with SQL 2008 onwards and performance with Oracle is superb.
Friday, August 30, 2013 9:06 PM
Hi Barry, Thank you so much for the reply. We are using File System on SQL 2008R2. We are not using Attunity, will look into it. So I took your code and tweaked it for File System and I think this gives me what I'm looking for. What do you think? Is this what I need? select '' as componentName, o.c.value('.', 'varchar(max)') connectionManagerName, cs.c.value('.', 'varchar(max)') connectionString from ( select cast(pkgblob.BulkColumn as XML) pkgXML from openrowset(bulk 'L:\default\ssis\Packages\account_address_load.dtsx', single_blob) as pkgblob ) t cross apply pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:ConnectionManager') cm(c) cross apply cm.c.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; DTS:Property[@DTS:Name="ObjectName"]') o(c) cross apply cm.c.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]') cs(c) union all select o.c.value('@name', 'varchar(max)') componentName, cs.c.value('@name', 'varchar(max)') propertyName, cs.c.value('.', 'varchar(max)') propertyValue from ( select cast(pkgblob.BulkColumn as XML) pkgXML2 from openrowset(bulk 'L:\default\ssis\Packages\account_address_load.dtsx', single_blob) as pkgblob ) t cross apply t.pkgXML2.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:ObjectData') cm(c) cross apply cm.c.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; pipeline/components/component') o(c) outer apply o.c.nodes('properties/property[(@name="TableName" or @name="SqlCommand" or @name="OpenRowset")]') cs(c)
Sunday, September 1, 2013 2:22 PM
Does it work for you? I would consider loading up the packages to a temp table and then query that. My advice to you is physically check a number of packages to make sure it's returning what you expect.
Good luck!
wBob