关于向sqlserver中插入xml格式数据的问题

Question

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<UserInfo>
  <UserLiveInfo>
  <LivePuid>111111111</LivePuid>
  <LiveEmailId>[email protected]</LiveEmailId>
  </UserLiveInfo>
  <WorkEmailId>[email protected]</WorkEmailId>
  <FirstName>first</FirstName>
  <LastName>last</LastName>
  <JobTitle>worker</JobTitle>
  <Department>unknow</Department>
  <CompanyAddress>
  <Address1>my</Address1>
  <Address3>ikonow</Address3>
  <City>pking</City>
  <State>jiangxi</State>
  <CountryId>11</CountryId>
  <ZipCode>111333</ZipCode>
  <Phone>1345558847</Phone>
  </CompanyAddress>
  <IsRegsysUpdateRequired>1</IsRegsysUpdateRequired>
  <UserOrganization>
  <OrganizationId>143</OrganizationId>
  </UserOrganization>
  <RegistrationCode>2C09DE65-2817-DF11-8FA2-00155DAC3105</RegistrationCode>
  <HomeAddress><HomeAddress3></HomeAddress3></HomeAddress>
  <NumberOfOrgEmployees>3</NumberOfOrgEmployees>
  <UserOrganization><OrganizationDesc>test</OrganizationDesc></UserOrganization>
  <DevelopmentInterest></DevelopmentInterest>
  <ProgrammingLanguagesInterest></ProgrammingLanguagesInterest>
  <TechnologyInterest></TechnologyInterest>
  <BestSearchForInformation></BestSearchForInformation>
  <ProductsUsed></ProductsUsed>
  <CommunicationPreference>11</CommunicationPreference>
</UserInfo>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT    *
                  FROM   OPENXML (@hdoc, '/UserInfo',2)  
WITH  (
                               LivePuid                     BIGINT 'UserLiveInfo/LivePuid'
                              ,LiveEmailId                  NVARCHAR(255) 'UserLiveInfo/LiveEmailId'                              
                              ,WorkEmailId                  NVARCHAR(255) 'WorkEmailId'
                              
                              ,FirstName                    NVARCHAR(128) 'FirstName'
                              ,LastName                     NVARCHAR(128) 'LastName'
                              
                              ,JobTitle                     NVARCHAR(128) 'JobTitle'
                              ,Department                   NVARCHAR(128) 'Department'
                              
                              ,Address1                     NVARCHAR(255) 'CompanyAddress/Address1'
                              ,City                         NVARCHAR(128) 'CompanyAddress/City'
                              ,[State]                      NVARCHAR(128) 'CompanyAddress/State'
                              ,CountryId                    TINYINT        'CompanyAddress/CountryId'
                              ,ZipCode                      NVARCHAR(20) 'CompanyAddress/ZipCode'
                              ,Phone                        NVARCHAR(20) 'CompanyAddress/Phone'                              
                              
                              ,IsRegsysUpdateRequired       BIT 'IsRegsysUpdateRequired' 
                              ,OrganizationId               INT 'UserOrganization/OrganizationId'                              
 ,RegistrationCode UNIQUEIDENTIFIER 'RegistrationCode'
                              
                              ,CompanyAddress3 NVARCHAR(128) 'CompanyAddress/Address3'
     ,HomeAddress3 NVARCHAR(128) 'HomeAddress/Address3'
  ,NumberOfOrgEmployees NVARCHAR(255) 'NumberOfOrgEmployees'
  ,OrganizationDescription NVARCHAR(255) 'UserOrganization/OrganizationDesc'
  ,DevelopmentInterest NVARCHAR(255) 'DevelopmentInterest'
  ,ProgrammingLanguagesInterest NVARCHAR(255) 'ProgrammingLanguagesInterest'
  ,TechnologyInterest NVARCHAR(255) 'TechnologyInterest'
  ,BestSearchForInformation NVARCHAR(255) 'BestSearchForInformation'
  ,ProductsUsed NVARCHAR(255) 'ProductsUsed'
  ,CommunicationPreference NVARCHAR(255) 'CommunicationPreference'                             
                          
         )
                 
  
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc上述代码提示错误：The XML parse error 0xc00ce55e occurred on line number 30, near the XML text "  <Programmin".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Element was not closed.'.
Msg 8179, Level 16, State 5, Line 40
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
请问如何修改？

Answer 1

帖子有点长 不过逻辑很简单 导入xml数据转化为table显示出来 但是这个错误不知道怎么解决 貌似xml格式没有问题啊。。

Answer 2

DECLARE @doc varchar(1000)
改为
DECLARE @doc varchar(8000)