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