How to convert a XML column content to a relationnal form

Question

Hi,

I wonder if it's possible (with a sql & xpath request) to "convert" a set of lines having in one nvarchar(max) column, some XML like this :

 <ArrayOfBedControlResult>   

    <BedControlResult>     

        <Location>S07Robot</Location>     
        <Name>Callage sur cible X1</Name>     
        <MeasureData>0</MeasureData>    
        <Status>1</Status>     
        <IsCorrected>false</IsCorrected>     
        <Notes>Calib_X1 : OK</Notes>   

    </BedControlResult>   

    <BedControlResult>     

        <Location>S07Robot</Location>
        <Name>Callage sur cible X2</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X2 : OK</Notes>

    </BedControlResult>   
    ...
    ...
</ArrayOfBedControlResult>


to a relationnal form like this :

Location      Name                        MeasureData        Status        IsCorrected        Notes
---------------------------------------------------------------------------------------------------------------
S07Robot    Callage sur cible X1    0                         1               false                  Calib_X1 : OK
S07Robot    Callage sur cible X2    0                         1               false                  Calib_X2 : OK
...
...

My xpath knowledge is near 0 and this problem seem to me too hard for a first contact ...
What I ve done for now is this to test with one field (Location), but nothing is returned :

SELECT Convert(XML,cp.Data).query('for $Location in /root/ArrayOfBedControlResult/BedControlResult/Location return $Location')  as Result FROM Core_BaseCustomProperty  cp


Does anybody have a idea how to do this ?

Thanks a lot for any help !
 
Yann.

Answer 1

Try this:

CREATE TABLE #tmp ( your_xml NVARCHAR(MAX) )
GO

INSERT INTO #tmp SELECT '
 <ArrayOfBedControlResult>    

    <BedControlResult>      
        <Location>S07Robot</Location>      
        <Name>Callage sur cible X1</Name>      
        <MeasureData>0</MeasureData>     
        <Status>1</Status>      
        <IsCorrected>false</IsCorrected>      
        <Notes>Calib_X1 : OK</Notes>    
    </BedControlResult>    
    <BedControlResult>      
        <Location>S07Robot</Location>
        <Name>Callage sur cible X2</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X2 : OK</Notes>
    </BedControlResult>    
</ArrayOfBedControlResult>'


SELECT 
	x.y.value('Location[1]', 'VARCHAR(30)') AS Location,
	x.y.value('Name[1]', 'VARCHAR(50)') AS Name,
	x.y.value('MeasureData[1]', 'DECIMAL(10,2)') AS MeasureData,
	x.y.value('Status[1]', 'INT') AS Status,
	x.y.value('IsCorrected[1]', 'VARCHAR(10)') IsCorrected,
	x.y.value('Notes[1]', 'VARCHAR(50)') AS Notes
FROM
	(
	SELECT CAST( your_xml AS XML ) AS your_xml
	FROM #tmp
	) t
	CROSS APPLY t.your_xml.nodes('ArrayOfBedControlResult/BedControlResult') x(y)

For further information, start with these two great articles:

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx

Introduction to XQuery in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx

Answer 2

Thanks a lot for this very useful answer.
It 's work fine with my application.

Yann.