WITH XMLNAMESPACES question. How to hadle variations per record in namespace def? WITH XMLNAMESPACES to include a wildcard?

Question

Querying XML Col where a namespace is defined, and elemets have namespace prefix, using WITH XMLNAMESPACES.  This works fine, but if a row's xml namespace def doesn't match the WITH XMLNAMESPACES def for that namespace, the query fails.  Can I define the WITH XMLNAMESPACES to use a wildcard so all rows will work regardless of any slight row-by-row variation?

For ex, these two rows match up to the last part.  If I could define the WITH XMLNAMESPACES like so ('http://schemas.microsoft.com/office/infopath/2003/myXSD/* ' AS my), both rows would respond.

• Row 1 <my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-08-29T18:23:34 "
• Row 2 <my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-07-13T20:25:06 "

Also, is there a performance hit for using wildcards in XQuery  select statements?  For ex:
SELECT  top 100 tp_LeafName
, xmlForm.exist('/*:myFields/*:FileAs') as chk -- example of wildcards as the ns prefix
,xmlForm.value('data((//*:myFields/*:FileAs)[1])', 'nvarchar(255)') AS FileAs
FROM  [Enterprise].[InfoPath].[Forms]
CROSS APPLY xmlForm.nodes ('//*:myFields/*:Contact') R(ClientMatter)

---

/bac

Answer 1

Couple of ways to do this:

CREATE TABLE #tmp ( x XML )
GO

INSERT INTO #tmp SELECT '<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-08-29T18:23:34 " />'
INSERT INTO #tmp SELECT '<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-07-13T20:25:06 " />'
GO

-- Wildcard
SELECT x.y.query('.')
FROM #tmp
	CROSS APPLY x.nodes('//*[local-name()="myFields"]') x(y)

-- Sequence Expression
;WITH XMLNAMESPACES( 
	'http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-08-29T18:23:34 ' AS a,
	'http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-07-13T20:25:06 ' AS b )
SELECT x.y.query('.')
FROM #tmp
	CROSS APPLY x.nodes('a:myFields, b:myFields') x(y)

Can't help feeling having two different URLs for the same namespace is bad practice but I'm guessing this is Sharepoint?

Answer 2

Thanks wBob.  The xml is generated in InfoPath, so the Namespaces are generated there, based on the date the form was created.

My work-around to use the asterisk as the namespace in the select statements seems to work, tho Im concerned that this will cause performance issues down the road.  Im reading http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx, and see that the PATH, PROPERTY, and VALUE index type behave differently wrt wildcards.

---

/bac

Answer 3

Test it out with your data; contrast with the second solution?  Let us know how you get on.