Help with XQuery

I am trying to query an XML Datatype column in SQL Server, but getting no data back, and I suspect the XQuery but don't know what I am doing wrong:

SELECT

nref.value('AdvTxnID[1]', 'integer') AdvTxnID,

nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode

FROM

myData CROSS APPLY [xmldata].nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">

<AdvanceTxn>

<AdvTxnID>4125281</AdvTxnID>

<SvcSysCode>MS</SvcSysCode>

<LoanID>8484664</LoanID>

<AdvTypeCode>P&amp;I</AdvTypeCode>

<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>

<AdvTxnAmt>.04</AdvTxnAmt>

<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>

</AdvanceTxn>

<TxnCount>1</TxnCount>

</AdvanceTxnDoc>

What am I doing wrong?

Need to declare namespace in your xquery. Two way to do it as following example:

declare @.x xml
set @.x =
N'<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">
<AdvanceTxn>
<AdvTxnID>4125281</AdvTxnID>
<SvcSysCode>MS</SvcSysCode>
<LoanID>8484664</LoanID>
<AdvTypeCode>P&amp;I</AdvTypeCode>
<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>
<AdvTxnAmt>.04</AdvTxnAmt>
<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>
</AdvanceTxn>
<TxnCount>1</TxnCount>
</AdvanceTxnDoc>'

with xmlnamespaces(default 'http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc')
SELECT
nref.value('AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

SELECT
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc";/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

|||

Worked great. Thanks!

-Kory

Help with XQuery

I'm trying to do my "Hello World" in XQuery and running into problems. Maybe you can advise.

DECLARE @.MyXML xml

SET @.MyXML = '<?xml version="1.0" encoding="ISO-8859-1" ?>

<bookstore><book category="COOKING">

<title lang="en">Everyday Italian</title>

<author>Giada De Laurentiis</author>

<year>2005</year>

<price>30.00</price>

</book><book category="CHILDREN">

<title lang="en">Harry Potter</title>

<author>J K. Rowling</author>

<year>2005</year>

<price>29.99</price>

</book><book category="WEB">

<title lang="en">XQuery Kick Start</title>

<author>James McGovern</author>

<author>Per Bothner</author>

<author>Kurt Cagle</author>

<author>James Linn</author>

<author>Vaidyanathan Nagarajan</author>

<year>2003</year>

<price>49.99</price>

</book><book category="WEB">

<title lang="en">Learning XML</title>

<author>Erik T. Ray</author>

<year>2003</year>

<price>39.95</price>

</book>

</bookstore>'

When I run this query in sql 2005 Query Editor I get the error message:

Msg 9332, Level 16, State 1, Line 41

XQuery [query()]: Syntax error near 'data', expected 'where', '(stable) order by' or 'return'.

SELECT @.myXml.query('

<booklist>

{

for $p in /bookstore/

where data($p/@.price) > 30.00

order by $p/title[1]

return $p/@.price

}

</booklist>

Any help appreciated,

Barkingdog

I assume you want to list all the book price > 30.00, order by book's title. If so, here is the query

SELECT @.myXml.query('

<booklist>

{

for $p in /bookstore/book

where data($p/price) > 30.00

order by $p/title[1]

return $p/price

}

</booklist>')

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
--
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcomMarco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Marco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom