April 20, 2010 at 8:12 am
Hi,
How can I limit number of query() result? Like in example below- I want to result only 3 positions
DECLARE @xml XML(TestSchema) = '
<root>
<EmploymentHistory>
<Position Name="Position 1">
<Company>1</Company>
<StartDate>2001-02</StartDate>
</Position>
<Position Name="Position 2">
<Company>Comapny 2</Company>
<StartDate>2002-02</StartDate>
</Position>
<Position Name="Position 3">
<Company>Comapny 3</Company>
<StartDate>2003-02</StartDate>
</Position>
<Position Name="Position 4">
<Company>Comapny 4</Company>
<StartDate>2002-03</StartDate>
</Position>
<Position Name="Position 5">
<Company>Comapny 5</Company>
<StartDate>2005-02</StartDate>
</Position>
</EmploymentHistory>
</root>
'
SELECT tab.c.query('
for $i in (EmploymentHistory/Position)
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
April 20, 2010 at 8:15 am
Use Top 3 in SELECT statement
April 20, 2010 at 8:20 am
Pulivarthi Sasidhar Chowdary (4/20/2010)
Use Top 3 in SELECT statement
It doesn't works- I want to limit XQuery results; not T-SQL
April 20, 2010 at 8:53 am
the TOp command is working for me to limit this example;
I couldn't use what you had posted, soemthing about the schema...
try this example with and without the TOP;
i get 3 rows without, and two rows with:
DECLARE @XML XML;
SELECT @XML =
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';
SELECT top 2
--R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node.query('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName,
R.Node.query('.').value('(/Customers/City/.)[1]','varchar(100)') AS CityName
FROM @XML.nodes('/ROOT/Customers') R(Node);
Lowell
April 20, 2010 at 9:03 am
In my code I use sorting in XQuery, and this is part of much more complicated example, so I still want to limit elements in XQuery 🙂
This is the schema for my example:
CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="StartDate">
<xsd:simpleType>
<xsd:restriction base="xsd:gYearMonth">
<xsd:minExclusive value="1940-01" />
<xsd:maxExclusive value="2020-01" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="root">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="EmploymentHistory">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="Position" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="Company" type="xsd:string" />
<xsd:element ref="StartDate" />
</xsd:sequence>
<xsd:attribute name="Name" type="xsd:string" use="required" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
April 20, 2010 at 9:20 am
tomas i learned something with this, thanks; i never get to use xml/xquery at work, so all my experience comes from testing ehre; your code gave me what i needed.
this command seems to limit to the first two rows, as an example; i was able to substitute different limits based on position, imlying i knew i wanted the first or last x rows, or a specific row.
SELECT tab.c.query('
for $i in (EmploymentHistory/Position[position() <= (2)])
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
SELECT tab.c.query('
for $i in (EmploymentHistory/Position[position() = (4)])
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
SELECT tab.c.query('
for $i in (EmploymentHistory/Position[position() >= (3)])
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
Lowell
April 20, 2010 at 9:31 am
Thanks for yuor answer.
Unfortunatelly it isn't exactly what I want, because I want correctly sorted names- BY StartDate.
Look at the comparison:
SELECT tab.c.query('
for $i in (EmploymentHistory/Position)
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
Results: Position 5 Position 3 Position 4 Position 2 Position 1
(this is the correct order)
SELECT tab.c.query('
for $i in (EmploymentHistory/Position[position() <= (2)])
order by fn:string($i/StartDate) descending
return (fn:data($i/@Name))')
FROM @xml.nodes('/root') tab(c)
Results: Position 2 Position 1
(not the same, because you limit the number of rows BEFORE ordering, so SQL Server gets 2 elements AND sorts them)
April 20, 2010 at 12:27 pm
Would you mind telling us the business reason behind it?
Usually, the order of similar elements/sub-nodes within a node level is irrelevant.
What is the reason you don't want to use T-SQL?
April 20, 2010 at 12:32 pm
Experimental reason 🙂 I want to measure how much slower is XML type than T-SQL, and by the way learn XQuery 🙂
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply