June 10, 2010 at 5:08 pm
Hello fellow developers. I am new to XQuery and have an issue. Based on the following XML I can't get any results unless I remove the namespace information at the top. Is there anyway to ignore the namespace. I have tried the following:
TABLE:
/****** Object: Table [dbo].[Students] Script Date: 05/27/2010 14:57:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Students](
[StudentName] [varchar](50) NULL,
[Labid] [int] NULL,
[Experimentid] [int] NULL,
[ExperimentChemical] [xml] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
---------------------------------------------------------------------------------
DATA:
INSERT INTO students
SELECT
'ABC',
3,
3,
'
<ATOM xmlns="urn://valence.aero/schemas/airtransport/ATOM/300" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn://valence.aero/schemas/airtransport/ATOM/300 ATOM.300.00.xsd">
<Destination>
<Identity />
</Destination>
<Source>
<Identity>
<Organisation>qantas.com.au</Organisation>
<Function>EI</Function>
<Location>SYD</Location>
<ProcessID>OgsAtomChangeNotification</ProcessID>
</Identity>
<GenerationTime>2010-05-24T11:14:08.355+10:00</GenerationTime>
</Source>
</ATOM>
'
---------------------------------------------------------------------------------
QUERY:
SELECT *
FROM students
WHERE experimentchemical.exist('/ATOM/Source/Identity[Location="SYD"]') = 1
ALSO TRIED:
SELECT *
FROM students
WHERE experimentchemical.exist('//Source/Identity[Location="SYD"]') = 1
June 24, 2010 at 9:31 am
Did you get an error about ANSI padding? Not sure why but when I did
SET ANSI_PADDING ON
SELECT *
FROM students
WHERE experimentchemical.exist('/ATOM/Source/Identity/Location="SYD"') = 1 it seems to work. Location is not a parameter for identity but rather an element under it, so I took out the [ and add a "/" instead.
More info here: http://sqlxml.org/faqs.aspx?faq=101
June 25, 2010 at 2:36 am
Ray's approach won't give you the requested result since it will always return all rows. Just test it with rows holding an XML string without the Location element or with values other than "SYD" for a given Location element...
You need to query the text attribute of the Location element.
Furthermore, you have a typed XML document. Therefore, you need to declare the namespace(s) within your SELECT statement. The tricky part is, that xmlns="urn://valence.aero/schemas/airtransport/ATOM/300" identifies the default namespace, that is used for values without a given namespace prefix, whereas xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" identifies the (unused) namespace prefix "xsi".
Side note: you can remove the xsi namespace declaration in the query below. It's just included to represent all namespaces defined in the XML document.
;
WITH XMLNAMESPACES
(
DEFAULT 'urn://valence.aero/schemas/airtransport/ATOM/300',
'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT s.*
FROM students s
WHERE experimentchemical.exist('//Location[text()="SYD"]') = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply