XML-Query

Transact-SQL
1

hi
i need to export data to xml
but, i can't success to add this line in first row

<?xml version="1.0" encoding="UTF-8" ?> 
SELECT 
ID,NAME
FROM MY_Table
FOR XML PATH('DOCUMENT')

so i get this result

<Document>
  <ID>037216082</ID>
  <NAME>037216082 ALIBABA</NAME>
  <TYPE>Other</TYPE>
</Document>

i want to get

<?xml version="1.0" encoding="UTF-8" ?>
    <Document>
      <ID>037216082</ID>
      <NAME>037216082 ALIBABA</NAME>
      <TYPE>Other</TYPE>
    </Document>
2

Try this:

select '<?xml version="1.0" encoding="UTF-8" ?>'
      +stuff((select id
                    ,name
                from my_table
                 for xml path('DOCUMENT')
             )
             ,1
             ,0
             ,'')
;
3

hi,

i need that will be in xml format.
what you wrote is not in format xml

i get error when i open it in explorer
thanks

4

when you get an error, post the error message

5

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

Switch from current encoding to specified encoding not supported. Error processing resource 'file://MyServerName/cba/fol.

<?xml version="1.0" encoding="UTF-8" ?>037216082037216082
6

The query I showed, produces:

<?xml version="1.0" encoding="UTF-8" ?><DOCUMENT><id>037216082</id><name>037216082 ALIBABA</name></DOCUMENT>

I fail to see the error, other than the field "type" is not shown - but hey, I copied your query, which also is missing this field.

If this the result you get, you are either using a different query, or didn't extract the field properly.

7

when i write this query in ssms it it not seem like xml (i mean when i click on the field so it isn't open xml window)

8

I just return text field, not xml field. Secondly I read somewhere that mssql is not happy with utf-8. You might want to try utf-16.

select '<?xml version="1.0" encoding="utf-16" ?>'
      +stuff((select id
                    ,name
                    ,[type]
                from my_table
                 for xml path('DOCUMENT')
             )
             ,1
             ,0
             ,''
            )
;

If you want a field to click on, you can convert the text field to xml field, but you lose the <?xml ..... ?> line.

select convert(xml
              ,'<?xml version="1.0" encoding="utf-16" ?>'
              +stuff((select id
                            ,name
                            ,[type]
                        from my_table
                         for xml path('DOCUMENT')
                     )
                     ,1
                     ,0
                     ,''
                    )
              )
;
9

i get error:
XML parsing: line 1, character 39, unable to switch the encoding

this my code

SELECT CONVERT(XML, '<?xml version="1.0" encoding="UTF-8" ?>'
      +stuff((SELECT DISTINCT
				ID = RC_CANDIDATE.ID_NUMBER,
				NAME = RC_CANDIDATE.ID_NUMBER + ' ' + RC_CANDIDATE.GIVEN_NAME1 +' '+ RC_CANDIDATE.SUR_NAME1, 
				TYPE = CASE WHEN CO_DOC_DOCUMENT_TYPES.DESCR1 IS NULL THEN 'OTHER' ELSE CO_DOC_DOCUMENT_TYPES.DESCR1 END 
			FROM RC_CANDIDATE_DOCUMENT
						LEFT JOIN CO_DOC_DOCUMENTS on CO_DOC_DOCUMENTS.DOCUMENT_ID = RC_CANDIDATE_DOCUMENT.DOC_ID
						LEFT JOIN RC_CANDIDATE on RC_CANDIDATE.CANDIDATE_ID = RC_CANDIDATE_DOCUMENT.CANDIDATE_ID
						LEFT JOIN CO_DOC_DOCUMENT_TYPES on RC_CANDIDATE_DOCUMENT.DOCUMENT_TYPE =CO_DOC_DOCUMENT_TYPES.DOCUMENT_TYPE_ID
			WHERE
				CANDIDATE_STATUS_CODE_ID = 12 
				AND
				RC_CANDIDATE.ID_NUMBER <> ''
				AND
				RC_CANDIDATE.ID_NUMBER = '200893154' --Test checking
			FOR XML PATH('Document')
             )
             ,1
             ,0
             ,'') )
10

Like I wrote, I don't think mssql likes utf-8. Try changing it to utf-16 or unicode