Xquery
Xquery. Using Xquery functions to manipulate data as needed.
Last updated:
Warning: Review and test in a non-production environment before running.
DECLARE @x XML
SET @x = '<fantasy>
<player Position="3B">
<name>Evan Longoria</name>
<team>Rays</team>
<slg>512</slg>
</player>
<player Position="OF">
<name>Carolos Gonzales</name>
<team>Rockies</team>
<slg>530</slg>
</player>
<player Position="C">
<name>Buster Posey</name>
<team>Giants</team>
<slg>486</slg>
</player>
</fantasy>';
SELECT @x.query('fantasy/player/team')
/*
<team>Rays</team> as link
<team>Rockies</team> as link
<team>Giants</team> as link
*/
-- position in xml
SELECT @x.query('/fantasy/player[3]/team')
/*
<team>Giants</team> as link
*/
-- attribute
SELECT @x.query('/fantasy/player[@Position="C"]/team')
/*
<team>Giants</team> as link
*/
-- text() function
SELECT @x.query('/fantasy/player[@Position="C"]/team/text()')
/*
Giants as link
*/
SELECT @x.value(
'(/fantasy/player[@Position="C"]/team/text())[1]'
,'varchar(50)') AS TeamName
/*
Giants as varchar(50)
*/
SELECT @x.query('
for $p in fantasy/player
return <slugging>
{($p/slg/text())[1]}
</slugging>
')
/*
<slugging>512</slugging>
<slugging>530</slugging>
<slugging>486</slugging>
*/
SELECT @x.query('
for $p in fantasy/player
let $t := $p/team
where $t="Rays"
return <slugging>
{($p/slg/text())[1]}
</slugging>
')
/*
<slugging>512</slugging>
*/
SELECT @x.query('
for $p in fantasy/player
let $t := $p/team
where $t="Rays"
order by ($p/slg)[1]
return <player name="{ $p/name/text() }">
<slugging>
{ ($p/slg/text())[1] }
</slugging>
</player>
')
/*
<player name="Evan Longoria">
<slugging>512</slugging>
</player>
*/
---------------------------------------------------------------------------------------------------
CREATE TABLE Actors (ID INT IDENTITY, ContactInfo XML)
GO
INSERT INTO Actors VALUES('
<Contact>
<Names>
<Name type="Legal">
<First>Thomas</First>
<Middle>Cruise</Middle>
<Last>Mapother</Last>
</Name>
<Name type="Stage">
<First>Tom</First>
<Middle></Middle>
<Last>Cruise</Last>
</Name>
</Names>
<Addresses>
<Address type="Primary">
<Street>12345 Main Street</Street>
<City>San Diego</City>
<State>CA</State>
<Zip>92130</Zip>
</Address>
<Address type="Other">
<Street>6200 Cruise Avenue</Street>
<City>San Fernando</City>
<State>CA</State>
<Zip>92126</Zip>
</Address>
</Addresses>
<Phones>
<Phone type="Mobile">8085554422</Phone>
<Phone type="Home">8085553399</Phone>
</Phones>
</Contact>
')
GO
INSERT INTO Actors VALUES('
<Contact>
<Names>
<Name type="Legal">
<First>Nicole</First>
<Middle>Mary</Middle>
<Last>Kidman</Last>
</Name>
</Names>
<Addresses>
<Address type="Primary">
<Street>1001 Oak Avenue</Street>
<City>San Diego</City>
<State>CA</State>
<Zip>92130</Zip>
</Address>
<Address type="Other">
<Street>555 Main Street</Street>
<City>San Fernando</City>
<State>CA</State>
<Zip>92126</Zip>
</Address>
</Addresses>
<Phones>
<Phone type="Mobile">8085554400</Phone>
<Phone type="Home">8085553300</Phone>
</Phones>
</Contact>
')
GO
select * from Actors
SELECT ContactInfo.value('(/Contact/Names/Name)[1]','varchar(50)') AS Name FROM Actors
SELECT ContactInfo.value('(/Contact/Names/Name)[2]','varchar(50)') AS Name FROM Actors
SELECT ContactInfo.value('(/Contact/Names/Name/First)[1]', 'varchar(50)') AS FirstName,
ContactInfo.value('(/Contact/Names/Name/Middle)[1]', 'varchar(50)') AS MiddleName,
ContactInfo.value('(/Contact/Names/Name/Last)[1]', 'varchar(50)') AS LastName
FROM Actors
SELECT ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last') AS LegalLastName,
ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last') AS StageLastName
FROM Actors
WHERE ID = 1
SELECT CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last/text()')) AS LegalLastName,
CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last/text()')) AS StageLastName
FROM Actors
WHERE ID = 1