Our editor have asked for us to provide a list of all of the documents which contain a link to a specific domain URL, so that they can go through and change them all. There are a over a hundred of these.
I can write a query to bring back the urlName property from cmsContentXml, as shown here:
select cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(200)') AS TheUrl,
* from [dbo].[cmsContentXml] x
where x.xml like '%ourdomain.hello.com%'
However, this doesn't give the full url as shown in the back office "Link to Document" property. Is there any way of getting the "Link to Document" through a SQL query?
I dont think that this is possible, for a few reasons:
The "Link to Document" is partly based on what hostname you set in "Culture and Hostnames" which i guess you could probobly get out in some kind of Ninja-SQL statement, but also a Node in Umbraco can have several URLs, depending on how many domains that are set up.
But most of all i think that this is impossible because of UrlProviders and ContentFinders. A nodes URL is not set on database level, it is set by code on a higher level, which I think makes it hard or impossible to get the URL in a SQL statement. Also if you´d where to use a package like "301 UrlRedirect" there could be a 301 rule for a specific node to redirect to another node, which makes it even harder to get it through a SQL-statement.
One way that you could quickly get a list of all nodes with their URL is to use the Sitemap.xml. If you dont already have a Sitemap.xml generated for your website, there are several packages avalible for generating this.
Hope this was helpful.
(If anyone has a Ninja-SQL-statement that can achive this I would be happy to stand corrected.) :)
Thanks for your reply Dennis. Having scanned all of the tables, I had expected that it wasn't possible at the SQL level, I'll try the Sitemap.Xml option.
I actually think that the sitemap.xml are a better option for you, since most of the sitemap.xml generators avalible ignores nodes that has no template (like components for example) and they take in consideration whether or not the node is published/schedule for publish.
So you will get a list of only relevant nodes to go over the URLs and i think that writing that in a SQL-query would be quite the job.
Retrieve "Link to Document" via SQL query
Hi there,
Our editor have asked for us to provide a list of all of the documents which contain a link to a specific domain URL, so that they can go through and change them all. There are a over a hundred of these.
I can write a query to bring back the urlName property from cmsContentXml, as shown here:
select cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(200)') AS TheUrl, * from [dbo].[cmsContentXml] x where x.xml like '%ourdomain.hello.com%'
However, this doesn't give the full url as shown in the back office "Link to Document" property. Is there any way of getting the "Link to Document" through a SQL query?
Hi Ben.
I dont think that this is possible, for a few reasons:
The "Link to Document" is partly based on what hostname you set in "Culture and Hostnames" which i guess you could probobly get out in some kind of Ninja-SQL statement, but also a Node in Umbraco can have several URLs, depending on how many domains that are set up.
But most of all i think that this is impossible because of UrlProviders and ContentFinders. A nodes URL is not set on database level, it is set by code on a higher level, which I think makes it hard or impossible to get the URL in a SQL statement. Also if you´d where to use a package like "301 UrlRedirect" there could be a 301 rule for a specific node to redirect to another node, which makes it even harder to get it through a SQL-statement.
One way that you could quickly get a list of all nodes with their URL is to use the Sitemap.xml. If you dont already have a Sitemap.xml generated for your website, there are several packages avalible for generating this.
Hope this was helpful.
(If anyone has a Ninja-SQL-statement that can achive this I would be happy to stand corrected.) :)
Thanks for your reply Dennis. Having scanned all of the tables, I had expected that it wasn't possible at the SQL level, I'll try the Sitemap.Xml option.
Happy to help.
I actually think that the sitemap.xml are a better option for you, since most of the sitemap.xml generators avalible ignores nodes that has no template (like components for example) and they take in consideration whether or not the node is published/schedule for publish.
So you will get a list of only relevant nodes to go over the URLs and i think that writing that in a SQL-query would be quite the job.
Best of luck to you, and have a great day!
is working on a reply...