Microsoft SQL Server forum - Yukon XQuery question

Author

Yukon XQuery question

Ken

2005-07-27, 1:28 pm

I am trying to query data in an XML variable and I am having trouble
getting the data out in the format that I would like. Given the
following XML:
declare @myDoc xml
set @myDoc = '<Ownership
TeamGuid="23EA393A-3926-4A55-8329-FE747593379D" TeamName=".NET
Framework">
<Roles>
<Role Guid="93BDB4E2-95A0-438D-96E4-43032EB2BA16" Name="API Review
PM Contact" ContactAlias="John.Smith" Inherited="0" ArtifactName="" />
<Role Guid="8A7145EF-B48B-4A93-BFA5-28B53995C022" Name="Architect"
ContactAlias="John.Doe" Inherited="0" ArtifactName="" />
<Role Guid="BF727283-B18C-415A-A38C-CEC15CD341D9" Name="Corp VP"
ContactAlias="None" Inherited="0" ArtifactName="" />
</Roles>
</Ownership>'

What I would like to do is run an XQuery against the variable and get
attribute data back in normal rows and columns like the following:

Guid Name
ContactAlias
------------------------------------ ---------------------
------------
93BDB4E2-95A0-438D-96E4-43032EB2BA16 API Review PM Contact John.Smith
8A7145EF-B48B-4A93-BFA5-28B53995C022 Architect John.Doe
BF727283-B18C-415A-A38C-CEC15CD341D9 Corp VP None

I've checked the Query and Value methods and have not been able to get
them to do this for me. Is this even possible w/XQuery? Do I need to
use OpenXML to do what I am trying to accomplish?

Thanks for your help!

David Portas

2005-07-27, 8:23 pm

Please post to the SQL Server 2005 groups:
http://communities.microsoft.com/ne...p=sqlserver2005

--
David Portas
SQL Server MVP
--

Erland Sommarskog

2005-07-27, 8:23 pm

Ken (kenjohnson77@hotmai
l.com) writes:
> I've checked the Query and Value methods and have not been able to get
> them to do this for me. Is this even possible w/XQuery? Do I need to
> use OpenXML to do what I am trying to accomplish?

Look at the nodes() function.

But as David said, you should use the specific SQL 2005 newsgroups for
SQL 2005 questions. Not the least for XML/XQuery issues this can
increase the quality of reponses dramatically. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links