SQL Server 2008R2 和创建 XML 文档 [英] SQL Server 2008R2 and creating XML document

问题描述

论坛上的第一篇文章，因为我真的被这个帖子困住了.

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

以下查询将有效的 XML 文档正确分配给@xTempXML 变量(xml 类型).注:文档长度(换算成varchar(max) = 711

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))

注意:连接的字符串长度=749，这是正确的.

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))

注意:这个变量的长度回到了711！

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

select @xFinalXML

变量仍然是一个有效的 XML 文档，只是没有版本信息

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

我做错了什么?

非常感谢所有帮助！

推荐答案

您错过了测试中的一个步骤.试试这个:

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.

根据您在做什么(即最终转换为 VARCHAR)，没有理由从 XML 数据类型开始.您也可以从 FOR XML 子句中删除 , TYPE 然后连接 @sXMLVersion + @xTempXML

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

发生这种情况的原因如下:xml 数据类型的限制

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

将 XML 数据存储在 xml 数据类型实例中时，不会保留 XML 声明 PI，例如 <?xml version='1.0'?>.这是设计使然.XML 声明 (<?xml ... ?>) 及其属性(版本/编码/独立)在数据转换为 xml 类型后丢失.XML 声明被视为 XML 解析器的指令.XML 数据在内部存储为 ucs-2.保留 XML 实例中的所有其他 PI.

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.

如何正确处理从 XML 字段/变量中提取数据的说明如下:XML 最佳实践(在文本编码"下)

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

SQL Server 2005 以 Unicode (UTF-16) 格式存储 XML 数据.从服务器检索的 XML 数据以 UTF-16 编码输出.如果您想要不同的编码，则必须对检索到的数据执行所需的转换.有时，XML 数据可能采用不同的编码.如果是，则在数据加载过程中必须小心.例如:

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:

• 如果您的文本 XML 是 Unicode(UCS-2、UTF-16)，您可以毫无问题地将其分配给 XML 列、变量或参数.
• 如果编码不是 Unicode 并且是隐式的，由于源代码页的原因，数据库中的字符串代码页应该与您要加载的代码点相同或兼容.如果需要，请使用 COLLATE.如果不存在这样的服务器代码页，则必须添加具有正确编码的显式 XML 声明.
• 要使用显式编码，请使用与代码页没有交互的 varbinary() 类型，或者使用适当代码页的字符串类型.然后，将数据分配给 XML 列、变量或参数.

• 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.

示例:显式指定编码
假设您有一个 XML 文档 vcdoc，存储为 varchar(max)，它没有显式的 XML 声明.下面的语句添加了一个编码为iso8859-1"的 XML 声明，连接 XML 文档，将结果转换为 varbinary(max) 以便保留字节表示，然后最后将其转换为XML.这使 XML 处理器能够根据指定的编码iso8859-1"解析数据，并为字符串值生成相应的 UTF-16 表示.

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)

<小时>

以下 S.O.问题是相关的:

---

The following S.O. questions are related:

• SQL Server 2008 - 将 XML 声明添加到XML 输出

如何添加xml编码到 SQL Server 中的 xml 输出

这篇关于SQL Server 2008R2 和创建 XML 文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！