can somebody help me. i have this simple record.

error_reference_uid item_number error_code
1234567 13579 odd
1234567 2468 even
and i want to create this xml.

<Errors error_reference_uid="1234567">
<Error item_number="13579" error_code="odd" />
<Error item_number="2468" error_code="even" />
</Errors>

i tried this

SELECT item_number, error_code, Qty FROM xml_file_item_errors FOR XML AUTO, TYPE

<xml_file_item_errors error_reference_uid="1234567" item_number="13579" error_code="odd" />
<xml_file_item_errors error_reference_uid="1234567" item_number="24680" error_code="even" />

and gave me this result.


please help.

Not the most elegant solution, but a working one.

-- temp table var to simulate data
DECLARE @xml_file_item_errors TABLE ( error_reference_uid INT, item_number INT, error_code VARCHAR(5) )

INSERT INTO @xml_file_item_errors
    
    SELECT    1234567, 13579, 'odd' UNION ALL
    SELECT    1234567, 2468, 'even'


SELECT    xfe.error_reference_uid,

        CONVERT ( XML,
                    ( SELECT    item_number, error_code
                    FROM        @xml_file_item_errors sub
                    WHERE        sub.error_reference_uid = xfe.error_reference_uid
                    FOR XML RAW ('Error') )
                )
    
        
FROM    ( SELECT DISTINCT error_reference_uid FROM @xml_file_item_errors ) xfe
FOR XML RAW ('Errors')