asked on

Querying SQL Stored XML data using XQuery

I have imported an XML file that has a Document Type Definition, The xml is structed as follows
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Suppliers SYSTEM "Supplier.dtd">
<Suppliers>
<Supplier>
<SupplierCode>311295</SupplierCode>
<SupplierName><![CDATA[mr and mrs smith]]</SupplierName>
</Suppliers>
</Supplier>

When Imported I have a single field of XML data in a table called XmlSuppliers
<Suppliers></Supplier><SupplierCode>311295</SupplierCode><SupplierName><![CDATA[mr and mrs smith]]</SupplierName></Suppliers></Supplier>

I want to use XQuery to query the XmlsSuppliers Table and to return as follows

Supplier Code Supplier Name
311295 mr and mrs smith

Iv searched the web and cant seem to find examples where the xml already exsists in a table, any help with the syntax would be appricated!

sweetfa2

SELECT SUPPLIERXML.query('
for $s in /Suppliers/Supplier
return
{data($s/SupplierCode)}    {data($s/SupplierName)}
'
FROM SUPPLERXMLTABLE

Open in new window

http://www.vldb2005.org/program/paper/thu/p1175-pal.pdf

MrDavidThorn

ASKER

getting the error SUPPLIERXML.query is not a user defined function or aggregate, or the name is ambiguous. I am using SQL Server 2008 Express edition

MrDavidThorn

ASKER

sorry see what I was doing wrong, have used

SELECT XMLFIELD.query('
for $s in /Suppliers/Supplier
return
{data($s/SupplierCode)} {data($s/SupplierName)}
'
FROM XmlSuppliers

Now getting error message XQuery[XmlSuppliers.XmlField.query()]: Syntax error near '{'

ASKER CERTIFIED SOLUTION

sweetfa2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrDavidThorn

ASKER

doh!