May 27, 2010 at 4:02 pm
Hi 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
May 8, 2013 at 7:35 am
I know this question is 2 years old, but thought I would answer it anyway.
I don't think you can 'ignore' the namespace, and your best bet may be to use a WITH XMLNAMESPACES clause:
SET ANSI_PADDING ON
GO
;WITH XMLNAMESPACES ('urn://valence.aero/schemas/airtransport/ATOM/300' AS ns)
SELECT *
FROM students
WHERE experimentchemical.exist('/ns:ATOM/ns:Source/ns:Identity[ns:Location="SYD"]') = 1
_________________________________
seth delconte
http://sqlkeys.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply