February 3, 2004 at 10:02 am
I have this piece of code to read from an xml doc, but it fails with a sql server error:
Error Message:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 168
XML parsing error: Switch from current encoding to specified encoding not supported.
Here is my code. Can some tell me what is wrong here. I am assuming that there is something wrong with the xml doc.
DECLARE @l_XMLDocId_Int INT
DECLARE @l_XMLDoc_Nva NVARCHAR(4000)
SET @l_XMLDoc_Nva = '
<?xml version="1.0" encoding="utf-8" ?>
<NewDataSet>
<ODM FileType="Snapshot" FileOID="Warp01" CreationDateTime="2004-01-06T014:00:00-05:00">
<Study OID="">
<GlobalVariables>
<StudyName>11110000000</StudyName>
<StudyDescription>Study Desc1</StudyDescription>
<ProtocolName>10</ProtocolName>
<ProtocolPhase>Initial</ProtocolPhase>
<ProtocolStatus>Unkonwn</ProtocolStatus>
<ProtocolAmendmentNumber>101010</ProtocolAmendmentNumber>
<ProtocolTargetAccrual>10</ProtocolTargetAccrual>
<ProtocolDrugDetails>
<DrugName>AZT</DrugName>
<INDNumber>500000868</INDNumber>
</ProtocolDrugDetails>
<ProtocolDiseases>
<DiseaseName>AIDS</DiseaseName>
</ProtocolDiseases>
<ProtocolPersonnel>
<PersonnelDetails>
<FirstName>Laurie</FirstName>
<MiddleName>S</MiddleName>
<LastName>Myers</LastName>
<Email>lmyers@nowhere.com</Email>
<RoleType>Data Manager</RoleType>
<RoleDescription>The person who performs the data model</RoleDescription>
</PersonnelDetails>
</ProtocolPersonnel>
<ProtocolSites>
<SiteDetails>
<SiteName>Harvard</SiteName>
<NetworkName>HNET</NetworkName>
<SiteAccrual>100</SiteAccrual>
<AccrualDate>11/01/2000</AccrualDate>
</SiteDetails>
</ProtocolSites>
</GlobalVariables>
</Study>
</ODM>
<SiteDetails>
<SiteName>Hopkins</SiteName>
<NetworkName>VRC</NetworkName>
<SiteAccrual>50</SiteAccrual>
<AccrualDate>06/10/2002</AccrualDate>
</SiteDetails>
<SiteDetails>
<SiteName>NYU</SiteName>
<NetworkName>HNET</NetworkName>
<SiteAccrual>10</SiteAccrual>
<AccrualDate>05/05/2001</AccrualDate>
</SiteDetails>
<PersonnelDetails>
<FirstName>Gary</FirstName>
<LastName>Nabel</LastName>
<Email>gnabel@somewhere.com</Email>
<RoleType>Data Collector</RoleType>
<RoleDescription>Data collector</RoleDescription>
</PersonnelDetails>
<PersonnelDetails>
<FirstName>Jeff </FirstName>
<LastName>Bamba</LastName>
<Email>jbamba@mehere.com</Email>
<RoleType>Nothing</RoleType>
<RoleDescription>something</RoleDescription>
</PersonnelDetails>
<ProtocolDiseases>
<DiseaseName>Cancer</DiseaseName>
</ProtocolDiseases>
<ProtocolDiseases>
<DiseaseName>Dai</DiseaseName>
</ProtocolDiseases>
<ProtocolDrugDetails>
<DrugName>Doxorubicin
</DrugName>
<INDNumber>500000869
</INDNumber>
</ProtocolDrugDetails>
<ProtocolDrugDetails>
<DrugName>Acyclovir AZT
</DrugName>
<INDNumber>500000872
</INDNumber>
</ProtocolDrugDetails>
<GlobalVariables>
<StudyName>00000222222</StudyName>
<StudyDescription>Study Desc2</StudyDescription>
<ProtocolName>12</ProtocolName>
<ProtocolPhase>End</ProtocolPhase>
<ProtocolStatus>Complete</ProtocolStatus>
<ProtocolAmendmentNumber>2202202</ProtocolAmendmentNumber>
<ProtocolTargetAccrual>44</ProtocolTargetAccrual>
<ProtocolDrugDetails>
<DrugName>DrugTest</DrugName>
<INDNumber>0011002</INDNumber>
</ProtocolDrugDetails>
<ProtocolDrugDetails>
<DrugName>DrugTest2</DrugName>
<INDNumber>2222222</INDNumber>
</ProtocolDrugDetails>
<ProtocolDrugDetails>
<DrugName>DrugTest3</DrugName>
<INDNumber>8888888</INDNumber>
</ProtocolDrugDetails>
<ProtocolDiseases>
<DiseaseName>Disease Test1</DiseaseName>
</ProtocolDiseases>
<ProtocolDiseases>
<DiseaseName>Disease Test2</DiseaseName>
</ProtocolDiseases>
<ProtocolDiseases>
<DiseaseName>Disease Test3</DiseaseName>
</ProtocolDiseases>
<ProtocolPersonnel>
<PersonnelDetails>
<FirstName>John</FirstName>
<MiddleName>A</MiddleName>
<LastName>James</LastName>
<Email>jj@testserver.com</Email>
<RoleType>Tester</RoleType>
<RoleDescription>This person test data</RoleDescription>
</PersonnelDetails>
<PersonnelDetails>
<FirstName>Akwasi </FirstName>
<LastName>Akomeah</LastName>
<Email>AA@servername.com</Email>
<RoleType>Unknown</RoleType>
<RoleDescription>Unknow</RoleDescription>
</PersonnelDetails>
<PersonnelDetails>
<FirstName>Josephine</FirstName>
<MiddleName>J</MiddleName>
<LastName>Annan</LastName>
<Email>jjanan@un.org</Email>
<RoleType>un inspector</RoleType>
<RoleDescription>un official</RoleDescription>
</PersonnelDetails>
</ProtocolPersonnel>
<ProtocolSites>
<SiteDetails>
<SiteName>Site1</SiteName>
<NetworkName>Network1</NetworkName>
<SiteAccrual>1</SiteAccrual>
<AccrualDate>01/01/2004</AccrualDate>
</SiteDetails>
<SiteDetails>
<SiteName>Site2</SiteName>
<NetworkName>Network2</NetworkName>
<SiteAccrual>2</SiteAccrual>
<AccrualDate>02/02/2004</AccrualDate>
</SiteDetails>
<SiteDetails>
<SiteName>Site3</SiteName>
<NetworkName>Network3</NetworkName>
<SiteAccrual>3</SiteAccrual>
<AccrualDate>03/03/2004</AccrualDate>
</SiteDetails>
</ProtocolSites>
</GlobalVariables>
<GlobalVariables></GlobalVariables>
</NewDataSet>
'
EXEC sp_xml_preparedocument @l_XMLDocId_Int OUTPUT, @l_XMLDoc_Nva
SELECT *
FROM OPENXML (@l_XMLDocId_Int, '/NewDataSet/ODM/Study/GlobalVariables',1)
WITH (StudyName varchar(50),
StudyDescription varchar(50),
ProtocolName varchar(50))
February 3, 2004 at 9:51 pm
I think that this was longer than 4000 characters. I changed the type to
DECLARE @l_XMLDoc_Nva VARCHAR(4000)
The errror suggested that the last element closers were begin cut off.
I changed it to
DECLARE @l_XMLDoc_Nva VARCHAR(8000)
It returned three Nulls (still not good).
I change from encoding="utf-8" to encoding="utf-16" and I got rid of your error. I just got the error that suggests that part of my string got chopped.
Now to fix the Null part.
Change the openxml clause to
FROM OPENXML (@l_XMLDocId_Int, '/NewDataSet/ODM/Study/GlobalVariables',2)
You had 1. 1 gets the fields from attributes, 2 from elements (thanks to Roy Folkner who showed this today).
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 4, 2004 at 7:36 am
Thank you very much. I worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply