SQL Server 2008R2 and creating XML document

Question

First post on forum since I am really stuck on this one.

The following query correctly assigns a valid XML document to the @xTempXML variable (of type xml). Note: The length of the document (converted to varchar(max) = 711

select @xTempXML = (
        select 
            PrescriberFirstName     as "row/prescriber/name/first",
            PrescriberLastName      as "row/prescriber/name/last",
            PrescriberAddress1      as "row/prescriber/address/line1",
            PrescriberAddress2      as "row/prescriber/address/line2",  
            PrescriberCity          as "row/prescriber/address/city",
            PrescriberState         as "row/prescriber/address/state",
            PrescriberZipCode       as "row/prescriber/address/zipcode",
            PatientFirstName        as "row/patient/name/first",
            PatientLastName         as "row/patient/name/last",
            PatientMiddleName       as "row/patient/name/middle",
            PatientAddress1         as "row/patient/address/line1",
            PatientAddress2         as "row/patient/address/line2", 
            PatientCity             as "row/patient/address/city",
            PatientState            as "row/patient/address/state",
            PatientZipCode          as "row/patient/address/zipcode",
            PatientFileID           as "row/patient/fileid",
            PatientSSN              as "row/patient/ssn",
            PatientDOB              as "row/patient/dob",
            DrugDescription         as "row/medicationprescribed/description",
            DrugStrength            as "row/medicationprescribed/strength",
            DrugDEASchedule         as "row/medicationprescribed/deaschedule",
            DrugQty                 as "row/medicationprescribed/qty",
            DrugDirections          as "row/medicationprescribed/directions",
            DrugFormCode            as "row/medicationprescribed/form",
            DrugDateWritten         as "row/medicationprescribed/writtendate",
            DrugEffectiveDate       as "row/medicationprescribed/effectivedate",
            DrugRefillQty           as "row/medicationprescribed/refill/qty",
            DrugRefillQtyQualifier  as "row/medicationprescribed/refill/qualifier",
            DrugNote                as "row/medicationprescribed/note",
            PharmacyStoreName       as "row/pharmacy/storename",
            PharmacyIdentification  as "row/pharmacy/identification",
            PharmacyAddress1        as "row/pharmacy/address/line1",
            PharmacyAddress2        as "row/pharmacy/address/line2",
            PharmacyCity            as "row/pharmacy/address/city",
            pharmacyState           as "row/pharmacy/address/state",
            pharmacyZipCode         as "row/pharmacy/address/zipcode"
        from 
            Rxarchive
         where ArchiveUUID=@ArchiveRefUUID
           and CreatedDT between @RptParamStartDT and  @RptParamStopDT
           and CHARINDEX(',' + PrescriberFID + ',', ',' + @RptParamFID + ',') > 0 
        FOR XML PATH(''), ROOT('result'), TYPE
        )

declare @sXMLVersion varchar(max) = '<?xml version="1.0" encoding="utf-8"?>'
select len(@sXMLVersion + convert(varchar(max),@xTempXML))

Note: The length of the concatenated strings = 749, which is correct.

set @xFinalXML = convert(xml,(@sXMLVersion + CAST(@xTempXML as varchar(max))))

select LEN(convert(varchar(max),@xFinalXML))

Note: The length of this variable is back to 711!

select @xFinalXML

The variable is still a valid XML document, just no version info

What am I doing wrong?

Any and all help greatly appreciated!

Answer 1

You missed a step in your testing. Try this:

SELECT CONVERT(XML, '<?xml version="1.0" encoding="utf-8"?>')

It will return an empty cell.

Based on what you are doing (i.e. converting to VARCHAR in the end), there is no reason to start with the XML datatype. You might as well remove the , TYPE from the FOR XML clause and then just concatenate @sXMLVersion + @xTempXML

The reason this is happening is noted here: Limitations of the xml Data Type

The XML declaration PI, for example, <?xml version='1.0'?>, is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration (<?xml ... ?>) and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2. All other PIs in the XML instance are preserved.

How to properly handle extracting data from an XML field / variable is noted here: XML Best Practices (under "Text Encoding")

SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from the server comes out in UTF-16 encoding. If you want a different encoding, you have to perform the required conversion on the retrieved data. Sometimes, the XML data may be in a different encoding. If it is, you have to use care during data loading. For example:

• If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to an XML column, variable, or parameter without any problems.
• If the encoding is not Unicode and is implicit, because of the source code page, the string code page in the database should be the same as or compatible with the code points that you want to load. If required, use COLLATE. If no such server code page exists, you have to add an explicit XML declaration with the correct encoding.
• To use an explicit encoding, use either the varbinary() type, which has no interaction with code pages, or use a string type of the appropriate code page. Then, assign the data to an XML column, variable, or parameter.

Example: Explicitly Specifying an Encoding
Assume that you have an XML document, vcdoc, stored as varchar(max) that does not have an explicit XML declaration. The following statement adds an XML declaration with the encoding "iso8859-1", concatenates the XML document, casts the result to varbinary(max) so that the byte representation is preserved, and then finally casts it to XML. This enables the XML processor to parse the data according to the specified encoding "iso8859-1" and generate the corresponding UTF-16 representation for string values.

SELECT CAST(
    CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX))
  AS XML)

---

The following S.O. questions are related:

• SQL Server 2008 - Add XML Declaration to XML Output

• How to add xml encoding <?xml version="1.0" encoding="UTF-8"?> to xml Output in SQL Server