|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Simpler XQuery Syntax?In SQL 2005, I’ve got an XML column called ‘CurrentConfig’ in a table called ‘Computers’. I just added a second schema to the schema collection. The initial schema had no associated namespace (i.e. the “no namespace†namespace). I recently added an extended schema with the namespace, ‘urn:ConfigSchema_V2’. The second schema is similar to the first, but has been extended and tightened down in certain areas. Look at the query below and notice that the ‘for’ statements are identical; the only difference is the namespace declaration in the second query. This works as intended, but it seems like a more concise representation of this whole statement would be possible. However, I couldn’t get the syntax gods to cooperate. ;-) Any simplification ideas? Thanks, Bill D. SELECT ComputerName from dbo.Computers where cast(CurrentConfig.query(' for $x in /CurrentConfiguration/Install/V where string($x/@n) = "TIER_SERVER" return data($x/@d) ') as varchar(1)) = '1' Or cast(CurrentConfig.query(' declare default element namespace "urn:ConfigSchema_V2"; for $x in /CurrentConfiguration/Install/V where string($x/@n) = "TIER_SERVER" return data($x/@d) ') as varchar(1)) = '1' ORDER BY 1 FOR XML AUTO Bill D. wrote:
Show quoteHide quote > Look at the query below and notice that the ‘for’ statements are identical; Does> the only difference is the namespace declaration in the second query. This > works as intended, but it seems like a more concise representation of this > whole statement would be possible. However, I couldn’t get the syntax gods > to cooperate. ;-) > > Any simplification ideas? > SELECT ComputerName > from dbo.Computers > where > cast(CurrentConfig.query(' > > for $x in /CurrentConfiguration/Install/V > where string($x/@n) = "TIER_SERVER" > return data($x/@d) > ') as varchar(1)) = '1' WHERE CurrentConfig.value(' (/CurrentConfiguration/Install/V[@n = "TIER_SERVER"]/@d)[1]', 'int') = 1 and > Or OR> > cast(CurrentConfig.query(' > declare default element namespace "urn:ConfigSchema_V2"; > > for $x in /CurrentConfiguration/Install/V > where string($x/@n) = "TIER_SERVER" > return data($x/@d) > ') as varchar(1)) = '1' CurrentConfig.value(' declare default element namespace "urn:ConfigSchema_V2"; (/CurrentConfiguration/Install/V[@n = "TIER_SERVER"]/@d)[1]', 'int') = 1 do what you want? Martin:
Yes, it all works. It just seems redundant to have to restate the identical 'for' statement, once for each namespace/schema. I thought there might be a more concise way to phrase this SELECT statement that would accomplish the same thing. Regards, Bill Show quoteHide quote "Martin Honnen" wrote: > Bill D. wrote: > > > Look at the query below and notice that the ‘for’ statements are identical; > > the only difference is the namespace declaration in the second query. This > > works as intended, but it seems like a more concise representation of this > > whole statement would be possible. However, I couldn’t get the syntax gods > > to cooperate. ;-) > > > > Any simplification ideas? > > > SELECT ComputerName > > from dbo.Computers > > where > > cast(CurrentConfig.query(' > > > > for $x in /CurrentConfiguration/Install/V > > where string($x/@n) = "TIER_SERVER" > > return data($x/@d) > > ') as varchar(1)) = '1' > > Does > > WHERE > CurrentConfig.value(' > (/CurrentConfiguration/Install/V[@n = "TIER_SERVER"]/@d)[1]', 'int') = 1 > > and > > > > > Or > > > > cast(CurrentConfig.query(' > > declare default element namespace "urn:ConfigSchema_V2"; > > > > for $x in /CurrentConfiguration/Install/V > > where string($x/@n) = "TIER_SERVER" > > return data($x/@d) > > ') as varchar(1)) = '1' > > OR > CurrentConfig.value(' > declare default element namespace "urn:ConfigSchema_V2"; > (/CurrentConfiguration/Install/V[@n = "TIER_SERVER"]/@d)[1]', 'int') = 1 > > do what you want? > > > > -- > > Martin Honnen --- MVP XML > http://msmvps.com/blogs/martin_honnen/ > Bill D. wrote:
> Yes, it all works. It just seems redundant to have to restate the identical Well with XQuery you can use *:foo to select 'foo' elements in any > 'for' statement, once for each namespace/schema. I thought there might be a > more concise way to phrase this SELECT statement that would accomplish the > same thing. >>> for $x in /CurrentConfiguration/Install/V namespace so you could do for $x in /*:CurrentConfiguration/*:Install/*:V to have a query that works against any possible namespace (even no namespace). You can also use WITH XMLNAMESPACES, eg
http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/359d2a08-9af3-44c6-976e-38bfb642974e Show quoteHide quote "Bill D." wrote: > All: > > In SQL 2005, I’ve got an XML column called ‘CurrentConfig’ in a table called > ‘Computers’. I just added a second schema to the schema collection. The > initial schema had no associated namespace (i.e. the “no namespace†> namespace). I recently added an extended schema with the namespace, > ‘urn:ConfigSchema_V2’. The second schema is similar to the first, but has > been extended and tightened down in certain areas. > > Look at the query below and notice that the ‘for’ statements are identical; > the only difference is the namespace declaration in the second query. This > works as intended, but it seems like a more concise representation of this > whole statement would be possible. However, I couldn’t get the syntax gods > to cooperate. ;-) > > Any simplification ideas? > > Thanks, > Bill D. > > > SELECT ComputerName > from dbo.Computers > where > cast(CurrentConfig.query(' > > for $x in /CurrentConfiguration/Install/V > where string($x/@n) = "TIER_SERVER" > return data($x/@d) > ') as varchar(1)) = '1' > > Or > > cast(CurrentConfig.query(' > declare default element namespace "urn:ConfigSchema_V2"; > > for $x in /CurrentConfiguration/Install/V > where string($x/@n) = "TIER_SERVER" > return data($x/@d) > ') as varchar(1)) = '1' > ORDER BY 1 > FOR XML AUTO > You could also do something like this:
SELECT ComputerName AS "@ComputerName" FROM dbo.Computers WHERE CurrentConfig.exist( '//*[local-name()="V"][@n = "TIER_SERVER"][@d = "1"]' ) = 1 FOR XML PATH('dbo.Computers') Even simpler! Show quoteHide quote "Bob" wrote: > You can also use WITH XMLNAMESPACES, eg > > http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/359d2a08-9af3-44c6-976e-38bfb642974e > > "Bill D." wrote: > > > All: > > > > In SQL 2005, I’ve got an XML column called ‘CurrentConfig’ in a table called > > ‘Computers’. I just added a second schema to the schema collection. The > > initial schema had no associated namespace (i.e. the “no namespace†> > namespace). I recently added an extended schema with the namespace, > > ‘urn:ConfigSchema_V2’. The second schema is similar to the first, but has > > been extended and tightened down in certain areas. > > > > Look at the query below and notice that the ‘for’ statements are identical; > > the only difference is the namespace declaration in the second query. This > > works as intended, but it seems like a more concise representation of this > > whole statement would be possible. However, I couldn’t get the syntax gods > > to cooperate. ;-) > > > > Any simplification ideas? > > > > Thanks, > > Bill D. > > > > > > SELECT ComputerName > > from dbo.Computers > > where > > cast(CurrentConfig.query(' > > > > for $x in /CurrentConfiguration/Install/V > > where string($x/@n) = "TIER_SERVER" > > return data($x/@d) > > ') as varchar(1)) = '1' > > > > Or > > > > cast(CurrentConfig.query(' > > declare default element namespace "urn:ConfigSchema_V2"; > > > > for $x in /CurrentConfiguration/Install/V > > where string($x/@n) = "TIER_SERVER" > > return data($x/@d) > > ') as varchar(1)) = '1' > > ORDER BY 1 > > FOR XML AUTO > > Bob and Martin:
Awesome!!! Both of your solutions work great and are much more concise than mine. I'll learn something by studying them both. Thanks much for all the help. Cheers, Bill Show quoteHide quote "Bob" wrote: > You could also do something like this: > > SELECT ComputerName AS "@ComputerName" > FROM dbo.Computers > WHERE CurrentConfig.exist( '//*[local-name()="V"][@n = "TIER_SERVER"][@d = > "1"]' ) = 1 > FOR XML PATH('dbo.Computers') > > Even simpler! > > "Bob" wrote: >  
Other interesting topics
FOR XML: How to prevent nesting elements
Obtain Data from XML column Obtaining data from an XML column to a document XML Shreading problem changing the results header with for xml looooooooong record XML Collating sequence Returning position() in the results Exporting to an XML file SQLXML XPath data with apostrophe |
|||||||||||||||||||||||
