XML nodes into one row in SQL



I have an xml file, and I have to put it's values into one row in SQL. I am new in SQL and I couldn't find any solution.


My XML file is:



<Images xmlns="http://ift.tt/1qGq3de" xmlns:xsi="http://ift.tt/ra1lAU">
<Image>
<Type>3</Type>
<Code>928</Code>
</Image>
<Image>
<Type>3</Type>
<Code>925</Code>
</Image>
<Image>
<Type>4</Type>
<Code>925</Code>
</Image>
</Images>


I can query it



DECLARE @xml xml
SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'C:\abc.xml', SINGLE_BLOB) as b;

;WITH XMLNAMESPACES(DEFAULT 'http://ift.tt/1qGq3de')
SELECT
im.value('(Type)[1]','int') AS 'ImageType',
im.value('(Code)[1]','int') AS 'ImageCode'
FROM
@xml.nodes('/Images/Image') AS X(im);


and I get a result



ImageType ImageCode
-----------------------
3 928
3 925
4 925


but I would like to get like



Image1Type Image1Code Image2Type Image2Code Image3Type Image3Code
-------------------------------------------------------------------------
3 928 3 925 4 925


How can I achieve this?


Many Thanks,


No comments:

Post a Comment