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

extracting individual nodes of XML Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 4:29 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 3:02 AM
Points: 124, Visits: 332
I have a issue with XML stored in a SQL table where I want to extract the information from each node into its own column in another table. All works well until I have multiple values under the same node (see XML below)

1. Easy to extract XML: NB: Booking is the root
<Booking Reference= "1111111" Created ="2012-09-24">
</Booking>

To extract the reference and the created I have used:

select
xml_data.query('data(//Booking/@Reference)') as BReference,
xml_data.query('data(//Booking/@Created)') as DateCreated from XMLData

This works just fine, giving me:

BReference DateCreated
11111111 25-09-2012

However my problem comes when I have TWO or more values in the same node:
I am trying to extract Hotel info individually so I get two hotel names into two rows not as
I currently have:

2. <Booking Reference= "1111111" Created ="2012-09-24">
-<Hotel Name="Villa Bella">
</Hotel>
-<Hotel Name="Sea View">
</Hotel>
</Booking>

When I use the same query principal by ADDING.

xml_data.query('data(//Booking/Hotel/@Name)') as HotelName

The result I get is:

BReference DateCreated HotelName
11111111 25-09-2012 Villa Bella Sea View

Whereas I want to pull out each line individually as:

BReference DateCreated HotelName
11111111 25-09-2012 Villa Bella
11111111 25-09-2012 Sea View

Any help greatfully received



Post #1364557
Posted Wednesday, September 26, 2012 4:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553

Use the 'nodes' function

DECLARE @t TABLE(xml_data XML)

INSERT INTO @t(xml_data) VALUES('
<Booking Reference= "1111111" Created ="2012-09-24">
<Hotel Name="Villa Bella">
</Hotel>
<Hotel Name="Sea View">
</Hotel>
</Booking>')

SELECT n1.value('@Reference','VARCHAR(10)') AS BReference,
n1.value('@Created','DATE') AS DateCreated,
n2.value('@Name','VARCHAR(20)') AS HotelName
FROM @t
CROSS APPLY xml_data.nodes('Booking') AS booking(n1)
CROSS APPLY booking.n1.nodes('Hotel') AS hotel(n2)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1364560
Posted Wednesday, September 26, 2012 4:50 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 3:02 AM
Points: 124, Visits: 332
Wow thanks Mark, I had dabbled with the nodes method, the path I chose was wrong.

Works a treat many thanks again.

Paul
Post #1364561
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse