Re: Querying xml data with SQL

There is a query() method available for the xml datatype also.

Here is an example that queries all the <child> elements under <parent>

SELECT xmlcol.query('//parent/child') FROM MY_XML_TABLE

Here is a query that selects only the <child> nodes with values "A" or "B"

SELECT xmlcol.query('//parent/child[./text()="A" or ./text()="B"]') FROM
MY_XML_TABLE

I hope this helps you get started. Don't hesitate to come back to the newsgroup
if you have more questions.

Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


"Cathy" <cathywigzell@xxxxxxxxxxx> wrote in message
news:1168858991.999652.272750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a table in Microsoft SQL Server 2005 with column of datatype
xml. The xml may contain multiple elements of the same type, e.g.
multiple <child> elements for a single <parent>. How can I query on the
<child> elements?

For example, what would be the correct syntax for this query where both
parent and child are elements within the xml...

SELECT parent FROM MY_XML_TABLE WHERE child='A' OR child='B'

I've already had a play around with xml.value and xml.nodes - but I
think I'm missing something crucial!

Many thanks for your help,

Cathy



.

Relevant Pages

  • Re: Error with reponse.write
    ... > ' Execute the query for the user in the directory ... and then view the page's source to copy the xml. ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Typed XML slows down query?!
    ... > Hi Martin, ... >>I have a performance problem with typed XML and a simple query. ... >> CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
  • setting dataset datarelation from database
    ... I want to set my dataset's datarelations based on the relationships ... I had a query I ... switched to using 'for xml explicit, ... If I run the query in sql query analyzer, I see the schema ...
    (microsoft.public.dotnet.framework.adonet)
Loading