Back devices: xquery

Showing posts with label xquery. Show all posts

Wednesday, March 28, 2012

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.

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

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

</Folder>

Could you help me? Thanks!

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

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

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

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

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 2 --
<Folder Name="Root" Id="a6dce8fe-749c-4e38-ab2f-3d03d9711b3d">
<Folder Name="Card" Id="b8dcf8fe-749c-4e38-ab2f-6d03d9711b8j">
</Folder>
</Folder>
//

|||

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

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

</Folder>

</Folder>
'

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

select @.my

//--Code-

<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.

Monday, March 26, 2012

How can I query the appointed node in XML with XQuery quickly?

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

The result I hope to get

================ Stored In XML field==========================

=====================================================

Some pepole give me a anwser, It work well, but when the size of the XML field is little big, it run very slowly, could you give a code which can run quickly!

//--Code -
declare @.my xml

</Folder>

</Folder>
'

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

select @.my

//--Code-

That ususally is done using recursion. But provided SQL Server doesn't support user-defined functions in XQuery it seems to be unfeasible to implement. Another workaround would be using XSLT.

Are you sure you need such kind of filtering? You said you only need to query some node, not filtering XML tree.

|||

Another way to write it would be to use the nodes() method to generate a relational rowset that contains Name, Id and ParentID (see the other posting for code samples), then write a recursive relational user-defined function that recomposes the hierarchy (see the FOR XML whitepaper for a code sample).

However, what I assume in your case, is that the logging of the changes is slowing you down. Have you checked what your data and IO disk load is? Do you have your log file on a different disk drive/disk spindle than your data?

Best regards

Michael

Friday, March 23, 2012

How can i output XQuery's key words such like "<,>" when using XQuery in SQL Se

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints ">" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate > (as you noticed we do). If you load the XML document that contains > into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'>','>')

I used replace ... convert ... , thus replace all the ">" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

sql

How can i output XQuery's key words such like "<,>" when using XQuery in SQ

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints ">" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'>','>')

I used replace ... convert ... , thus replace all the ">" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

Wednesday, March 28, 2012

How can I query the node in XML with XQuery?

How can I query the node in XML with XQuery?

Monday, March 26, 2012

How can I query the appointed node in XML with XQuery quickly?

Friday, March 23, 2012

How can i output XQuery's key words such like "<,>" when using XQuery in SQL Se

How can i output XQuery's key words such like "<,>" when using XQuery in SQ

Back devices

Blog Archive

About Me