• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

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!


0
MrDavidThorn
Asked:
MrDavidThorn
  • 3
  • 2
1 Solution
 
sweetfa2Commented:
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
0
 
MrDavidThornAuthor Commented:
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
0
 
MrDavidThornAuthor Commented:
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 '{'
0
 
sweetfa2Commented:
SELECT XMLFIELD.query('
for $s in /Suppliers/Supplier
return
{data($s/SupplierCode)}    {data($s/SupplierName)}
')
FROM XmlSuppliers

Open in new window

0
 
MrDavidThornAuthor Commented:
doh!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now