Back devices: How can I query the node in XML with XQuery?

1. I store the xml info below in the XML field Demographics in SQL 2005, I hope to query all the node info with XQuery.

The result just like
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>

but I can't get the correct result using
select Demographics.query('/Folder') from store

2. Furthermore, How can I query the Name attribute of the Folder node by ID of Folder using XQuery,
If so, I can get the Name (such as "Root") after I know the ID "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"

3. Can I get the result below using XQuery? (Notice: the sub Name="Card" have no content )

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
</Bookmark>

</Folder>

Could you help me? Thanks!

=========================XML Info=====================================

<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
</Folder>

======================XML Info=====================================

Before I get to the questions: There are many examples in our MSDN whitepapers. Two in particular will be very informative -

1) XML feature overview: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
2) XQuery: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp

Now for your questions:

1) I ran your code and got back the expected result - the full XML. What was your expection?

2) Try
SELECT Demographics.value ('(//Folder[@.Id = "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d"]/@.Name)[1]', 'nvarchar(64)')
FROM store

When your table contains more than one row, you will get a NULL value from the rows that do not contain a <Folder> with Id "a6dce8fe-749c-4e38-ab2f-3d03d9711b3d". Eliminate those rows using the exist() method in a (T-SQL) WHERE clause or write an outer SELECT statement. There are examples in the whitepapers above.

3) Here is one way of writing your query:
SELECT Demographics.query ('
for $f in /Folder
return
<Folder Name="{$f/@.Name}" Id="{$f/@.Id}">
{for $b in $f/Bookmark
return <Bookmark> {$b/Title}{$b/Url}</Bookmark>}
<Folder Name="{$f/Folder/@.Name}" Id="{$f/Folder/@.Id}">
{$f/Folder [@.Name ne "Card"]/Bookmark}
</Folder>
</Folder>')
FROM store

Not sure what you are trying to do - if you can tell us more, we might be able to suggest other solutions.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||

When I run "select Demographics.query('/Folder') from store", I get the Result 1, but what I expect is Result 2 ! How can I get Result 2 ？

//-- Result 1 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark>
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark>
<Title>We will e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-12-5</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark>
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark>
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-08-5</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>
//--

//-- Result 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

Your expectation is not inline with what the XPath or XQuery specification defines should be the result. The children of the folder node are part of that node, and thus they are returned. You can get the results you are looking for with an XQuery statement like this:

select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')

However, this will only work for "Folder" elements which are nested at two levels deep. You will find difficulty in supporting more generic scenarios since user defined functions are not supported in our XQuery implementation.

Another option would be to use our DML language to remove elements which are not named "Folder":

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x

This works by removing all of the nodes which are not named "Folder", along with any text content.

-John

|||

Thank you very much!

The following code you wrote is not OK
select @.x.query('
for $folder in /Folder
return
element Folder { ($folder/@.*,
element Folder { $folder/Folder/@.* }
)
}
')
The following code is OK!!!

set @.x.modify('
delete (//*[local-name(.) != "Folder"], //text())
')

select @.x
but what does the "local-name" mean?

//--Code-
declare @.my xml

set @.my='
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Bookmark BId="f8dce8hj-846c-4e38-ab2f-6d03d9711b80">
<Title>CodeGuru Forums - ASP.NET</Title>
<Url>http://www.codeguru.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2005-12-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>
<Bookmark BId="fgdce3ak-846c-4e38-ab2f-8i03d9711b23">
<Title>We ll e-mail your press release</Title>
<Url>http://www.dpdirectory.com/</Url>
<Description>This is a good site</Description>
<InputDate> 2004-11-23</InputDate>
<IsPrivate>False</IsPrivate>
</Bookmark>

<Folder Name="Card1" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
<Bookmark BId="ghdce3ak-456c-4e38-ab2f-5h02d9711b67">
<Title>Welcome to ePassporte</Title>
<Url>https://www.epassporte.com/</Url>
<Description>Very Good</Description>
<InputDate> 2004-08-12</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
<Bookmark BId="fkdfh3a8-456c-6y38-jk2f-5h0gh9711b45">
<Title>Keystone DreamCard</Title>
<Url>https://www.mydreamcardonline.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-25</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>

<Folder Name="Card1in1" Id="l9dcf8fe-689c-0935-fghj-7u03d9711b5t">
<Bookmark BId="ghdfh3a8-896c-6y40-jkfg-5h0gh9711b89">
<Title>The News of CNN</Title>
<Url>https://www.cnn.com</Url>
<Description>Please note</Description>
<InputDate> 2004-09-23</InputDate>
<IsPrivate>True</IsPrivate>
</Bookmark>
</Folder>

</Folder>

</Folder>
'

set @.my.modify('delete (//*[local-name(.) != "Folder"], //text()) ')

select @.my

//--Code-

|||local-name returns the "local name" of a node. All element names are made up of two parts (this is what is called a qualified name, or QName): a) the namespace uri, and b) the local name. The namespace uri is usually indicated by a prefix which is bound to the actual namespace uri. For example, take this node:

<a:foo xmlns:a="bar" />. It has "bar" as its namespace uri, and "foo" as its local name.

So what the query does that I provided is it checks every element in the document, and if the local name does not equal "Folder", it deletes it from the document.

Wednesday, March 28, 2012

How can I query the node in XML with XQuery?

No comments:

Post a Comment

Back devices

Blog Archive

About Me