Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

XML Workshop XVII - Writing a LOOP to process XML elements in TSQL Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2008 9:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item XML Workshop XVII - Writing a LOOP to process XML elements in TSQL

.
Post #474491
Posted Wednesday, March 26, 2008 12:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2008 2:07 AM
Points: 21, Visits: 21
Cool One.!!
Another way to fetch the XML Element without loop will be by using the XQuery function Text.

For same xml this will work.

SELECT T.c.value('(Name/text())[1]', 'varchar(256)') as Name,T.c.value('(Department/text())[1]', 'varchar(256)') as Department
FROM @x.nodes('/Employees/Employee') T(c)
Post #474522
Posted Wednesday, March 26, 2008 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
Another way using xquery if you have multiple Name elements as noted in this blog entry
http://blogs.msdn.com/mrorke/archive/2005/07/21/441554.aspx

SELECT T.ref.value('.', 'varchar(256)') AS Employee
FROM
(
select [Xml]=@x.query('
for $i in data(/Employees/Employee/Name) return
element temp { $i }
')
) A
cross apply A.Xml.nodes('/temp') T(ref)



Post #474636
Posted Wednesday, March 26, 2008 6:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Does anyone know whether the XML or T-SQL count() functions are equivalent in execution, or is one more efficient than the other.

Is there any significant difference between...
 SELECT
@max = @x.query('
{ count(/Employees/Employee) }
').value('e[1]','int')

and
select @max = count(*) from
@x.nodes('/Employees/Employee')
e(x)

?

The query plans indicate that the cost of the first is twice the cost of the second. Is this true in practice?


Derek
Post #474654
Posted Wednesday, March 26, 2008 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:04 AM
Points: 4, Visits: 46
just do this to retrieve the element count for the loop - we've had to do this a lot..
select @max = @x.value('fn:count(/Employees/Employee)','int')


or you could even include the xpath in the while loop definition:
while @i <= (@x.value('fn:count(/Employees/Employee)','int'))
begin
print @i
set @i = @i + 1
end


there are a lot of fn: xpath functions that work really well for different situations - BOL has a listing of them, just search for "fn:"
Post #474693
Posted Wednesday, March 26, 2008 7:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
You can retrieve all names without a CTE and CROSS APPLY as well:


DECLARE @x XML;

SET @x = '<Employees>
<Employee ID="101">
<Name>Jacob</Name>
<Department>IT</Department>
</Employee>
<Employee ID="354">
<Name>Steve</Name>
<Department>IT</Department>
</Employee>
<Employee ID="456">
<Name>Bob</Name>
<Department>IT</Department>
</Employee>
<Employee ID="478">
<Name>Joe</Name>
<Department>IT</Department>
</Employee>
<Employee ID="981">
<Name>Louis</Name>
<Department>IT</Department>
</Employee>
</Employees>';

-- select "Name"
WITH Num(i)
AS
(
SELECT 1
UNION ALL
SELECT i + 1
FROM Num
WHERE i < (SELECT @x.value('count(/Employees/Employee)','int') )
)
SELECT x.value('Name[1]', 'VARCHAR(20)')
FROM Num
CROSS APPLY @x.nodes('/Employees/Employee[position()=sql:column("i")]') e(x);

That's if you really want to use the position() function. Without using position() it gets a little simpler:

SELECT x.value('Name[1]', 'VARCHAR(20)') 
FROM @x.nodes('/Employees/Employee') e(x);

Post #474713
Posted Wednesday, March 26, 2008 8:08 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
This has been helpful, after I spent 0.5 day yesterday trying to figure out XQuery in T-SQL as DBA
in the end, I cast XML to VARCHAR and PATINDEX it....

Can anybody show/confirm how I can do this as SET operation on a table T with X as a XML column, instead of working on 1 XML @x at a time

e.g. can I do this to get all Names, say in a department table T that has an Employee XML column X?

SELECT x.value('Name[1]', 'VARCHAR(20)')
FROM T.X.nodes('/Employees/Employee') e(x);

Thanks in advance
I really need a quick lesson in XQuery


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #474739
Posted Wednesday, March 26, 2008 9:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:04 PM
Points: 126, Visits: 500
Sorry to be Lazy or maybe just an american?

Anyway I know there is a meaning behind this in the code.

e(x)

I just can't seem to figure out what. One of the Replies used T(c) instead. I'm sure it's some type of XML formatting paramater I just have no idea and am curious.

Thanks,

M



Post #474850
Posted Wednesday, March 26, 2008 9:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
After the .nodes() method you must create a table and column alias. e is the table alias, c is the column alias.
Post #474872
Posted Wednesday, March 26, 2008 11:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 6, 2013 2:42 PM
Points: 58, Visits: 266
This is more elegant.

SELECT
Table1.Column1.value('.','VARCHAR(20)') AS Employee_Name
FROM
@XML_Employee_Name.nodes('/Employees/Employee/Name[1]') AS Table1(Column1)
Post #474941
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse