xquery - get grandparent attribute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

xquery - get grandparent attribute

Author

Topic

itsMe
Starting Member

1 Post

Posted - 2011-03-28 : 02:24:31

Hi Guys,

Im new to xml and xquery and wanted to know how to retrieve the grandparents attribute value. I was able to extract the other values I wanted using the the following script:

select
usage.value('@id', 'varchar(10)') as carrierref,
usage.value('call_date_time', 'varchar(20)') as [calldatetime],
usage.value('called_number', 'varchar(50)') as [numberdialed],
usage.value('call_duration_seconds', 'int') as [duration],
usage.value('call_charge_cents', 'numeric(7,2)')/100 as [carriercharge],
usage.value('call_type', 'varchar(10)') as [calltype],
usage.value('description', 'varchar(50)') as [desc],
usage.value('rate_id', 'varchar(10)') as [carrierrate]
from testdb..xmlcdr
cross apply data.nodes('/usage/fullservice_connection/period/call') as cdr(usage)

I need to extract the value of the attribute "service_telephone". Your help will be much appreciated.

Here's my sample xml:

<?xml version="1.0" ?>
<usage>
<fullservice_connection connection_id="517317" contract_id="661175" service_telephone="0732991117">
<period>
<start_date>22-Mar-2011</start_date>
<end_date>27-Mar-2011</end_date>
<call id="16652510">
<call_date_time>22-Mar-2011 09:05:32</call_date_time>
<called_number>0732994870</called_number>
<call_duration_seconds>74</call_duration_seconds>
<call_charge_cents>14</call_charge_cents>
<call_type>LOCAL</call_type>
<description>Beenleigh</description>
<rate_id>10000</rate_id>
</call>
<call id="16652533">
<call_date_time>22-Mar-2011 10:30:09</call_date_time>
<called_number>1300276468</called_number>
<call_duration_seconds>183</call_duration_seconds>
<call_charge_cents>25</call_charge_cents>
<call_type>SERVICE</call_type>
<description>13/1300</description>
<rate_id>10008</rate_id>
</call>
<call id="16652565">
<call_date_time>22-Mar-2011 13:02:48</call_date_time>
<called_number>1300724272</called_number>
<call_duration_seconds>680</call_duration_seconds>
<call_charge_cents>25</call_charge_cents>
<call_type>SERVICE</call_type>
<description>13/1300</description>
<rate_id>10008</rate_id>
</call>
</period>
</fullservice_connection>
</usage>

Thank you! :-)

Best Regards
Stephen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-28 : 11:18:13

xml ain't my thing, so I copy from Peso. This may help

http://weblogs.sqlteam.com/peterl/archive/2009/06/04/Extract-XML-structure-automatically-part-2.aspx

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 21:05:28

You can use the .. notation (two dots) to navigate to the parent. So in your case it would be something like

usage.value('../../@service_telephone','varchar(32)') as ServicePhoneNumber

Navigating to the parent is inefficient, so if performance is a consideration, you may want to shred to the parent level perhaps using another cross-apply and then get the attribute for example, like this:


      usage.value('rate_id', 'varchar(10)') as [carrierrate],
      conn.value('@service_telephone','varchar(32)') as Phone
from  testdb..xmlcdr
      cross apply data.nodes('/usage/fullservice_connection/period/call') as cdr(usage)
      cross apply data.nodes('/usage/fullservice_connection') as cdr2(conn)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources