论坛上的第一篇文章,因为我真的被这个问题困住了。
以下查询正确地将有效的 XML 文档分配给 @xTempXML 变量(类型为 xml)。注:文档的长度(转换为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,这是正确的。
set @xFinalXML = convert(xml,(@sXMLVersion + CAST(@xTempXML as varchar(max))))
select LEN(convert(varchar(max),@xFinalXML))
注意:这个变量的长度又回到了711!
select @xFinalXML
该变量仍然是有效的 XML 文档,只是没有版本信息
我究竟做错了什么?
非常感谢任何和所有帮助!