Â¥Ö÷

Çë½ÌÈçÏÂXSD ¶¨ÒåÎÊÌâ I have two tables and a xml, xsd file ʹÓÃSQLXMLBulkLoad load xml Êý¾Ý½øÈëÁ©±íʱ³ö´í: "Invalid value for column "NAME"", "NAME" ÊÇÒ»¸öǶÌ×ÔªËص«ÔÚ±íÖÐûÓиÃ×ֶΡ£ ±íʾ FIRST-NAME and LAST-NAME. ¸ßÊÖÖ´½Ìwhat's wrong for the xsd file "NAME" parts¡£ лл. 1. tables: ---------------------------------------------------------------------------------------------------- PAYOR(bankID, PRNAME,PENAME, TRN,BREF,DTM097, DTM107,TTLAMT,TRNCNT) ENT (bank, FIRST-NAME,LAST-NAME,ACNUM,CR,CREF,DTM109) ENT(bank) is foreign key refer to PAYOR(bankID) -------------------------------------------------------------------------------------------------------- 2. XML File <?xml version="1.0" ?> <ROOT>    <PAYOR bankID="001">       <PRNAME>BANK</PRNAME>       <PENAME>CUST</PENAME>       <TRN>2222</TRN>       <BREF>3333</BREF>       <DTM097>4444</DTM097>       <DTM107>1111</DTM107>       <TTLAMT>300</TTLAMT>       <TRNCNT>2</TRNCNT>    </PAYOR>    <ENT bank="001">       <NAME>          <FIRST-NAME>AAA</FIRST-NAME>          <LAST-NAME>AAA</LAST-NAME>       </NAME>       <ACNUM>001001001</ACNUM>       <CR>100</CR>       <CREF>1111</CREF>       <DTM109>1111</DTM109>    </ENT>    <ENT bank="001">       <NAME>          <FIRST-NAME>BBB</FIRST-NAME>          <LAST-NAME>BBB</LAST-NAME>       </NAME>       <ACNUM>002002002</ACNUM>       <CR>200</CR>       <CREF>1111</CREF>       <DTM109>1111</DTM109>    </ENT> </ROOT> ---------------------------------------------------------------------------- 3. Create a XSD file for the XML to database mapping: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified"> <xs:annotation>   <xs:appinfo>   <sql:relationship name="BANK-ENT" parent="PAYOR" parent-key="bankID" child="ENT" child-key="bank">   </sql:relationship> </xs:appinfo> </xs:annotation> <xs:element name="ACNUM" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="BREF" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="CR" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="CREF" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="DTM109" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="ROOT">  <xs:complexType>  <xs:sequence>                          <xs:element ref="PAYOR" maxOccurs="unbounded"/>                          <xs:element ref="ENT" maxOccurs="unbounded"/>  </xs:sequence>  </xs:complexType>  </xs:element> <xs:element name="ENT" sql:relation="ENT" sql:relationship ="BANK-ENT">  <xs:complexType>  <xs:sequence>   <xs:element ref="NAME"/>   <xs:element ref="ACNUM"/>                         <xs:element ref="CR"/>                      <xs:element ref="CREF"/>   <xs:element ref="DTM109"/>  </xs:sequence>                     <xs:attribute name="bank" type="xs:string" use="required" sql:datatype="varchar(128)"/>  </xs:complexType>  </xs:element> <xs:element name="FIRST-NAME" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="LAST-NAME" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="NAME">  <xs:complexType>  <xs:sequence>   <xs:element ref="FIRST-NAME"/>   <xs:element ref="LAST-NAME"/>  </xs:sequence>  </xs:complexType>  </xs:element> <xs:element name="PAYOR" sql:relation="PAYOR">                 <xs:complexType>  <xs:sequence>   <xs:element ref="PRNAME"/>   <xs:element ref="PENAME"/>   <xs:element ref="TRN"/>   <xs:element ref="BREF"/>   <xs:element ref="DTM097"/>   <xs:element ref="DTM050" minOccurs="0"/>   <xs:element ref="DTM107"/>   <xs:element ref="TTLAMT"/>   <xs:element ref="TRNCNT"/>  </xs:sequence>  <xs:attribute name="bankID" type="xs:string" use="required" sql:datatype="varchar(128)"/>  </xs:complexType>  </xs:element> <xs:element name="PENAME" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="PRNAME" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="TRN" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="DTM097" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="DTM050" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="DTM107" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="TTLAMT" type="xs:string" sql:datatype="varchar(128)"/> <xs:element name="TRNCNT" type="xs:string" sql:datatype="varchar(128)"/> </xs:schema>    Êղؠ  ·ÖÏí   ¶¥(0)

	µÚ2Â¥

Õâ¸öÓ¦¸Ã²»ÊÇXSDµÄÎÊÌâ°É£¬Õâ¸öXMLÊÇvalidµÄ¡£Ò²¾ÍÊÇ˵ͨ¹ýÁËxsdµÄ¼ìÑ飬ÎÒ¾õµÃÊÇÄãµÄSQLXMLBulkLoadµÄÎÊÌ⣬ËüÄÜ´¦ÀíÔõÑùµÄXMLÄØ£¬ÊÇ·ñÄܹ»ÈϵÃÄãµÄ<NAME>±êǩϵÄÄÇÁ½¸ö½ÚµãÄØ£¿ËûÊÇÖ»ÈϵÚÒ»²ã»¹ÊÇÈÏ×îºóÒ»²ãÄØ£¿ÓпÉÄÜÖ»ÊÇÈϵÃ×î¼òµ¥µÄ£Ø£Í£Ì£¬Ä㽫ÄÇÁ½¸ö×Ö¶ÎÓãΣÁ£Í£Å°üÆðÀ´£¬ÔڽṹÉÏÇåÎúÁË£¬µ«ÊÇÓпÉÄܾͲ»±»³ÌÐòÈÏÁËÄØ¡£¸öÈ˹۵ã½ö¹©²Î¿¼¡£

	µÚ3Â¥

SQLXMLBulkLoad ÓõÄÊÇ  SQLXML 3.0. ÔÚËûµÄÀý×ÓÀïûÓп´µ½=ǶÌ×µÄÁ½²ãµÄ  ¡£ ÔÚÕâÀïÎÒÃÇûÓÐÖ¸¶¨  " NAME" Ó°Éäµ½±íµÄ×ֶΡ£ Èç¹ûÒª¹ýÂ˵ô  "<NAME>" and "</NAME>"£¬ ÈçºÎµÄ·½·¨±È½ÏºÃЩ>

	µÚ4Â¥

¿ÉÒÔÓÃxslt½«¸Ãxml½øÐÐת»»£¬½«Name±êÇ©È¥µô£¬È»ºóÔÙʹÓÃSQLXMLBulkLoad¡£ <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format"> <xsl:output method="xml" version="1.0" indent="yes"/> <xsl:template match="ROOT">  <xsl:copy>   <xsl:apply-templates/>  </xsl:copy> </xsl:template> <xsl:template match="PAYOR ">  <xsl:copy-of select="."/> </xsl:template> <xsl:template match="ENT">  <xsl:copy >  <xsl:for-each select="attribute::*">   <xsl:attribute name="{name(.)}">    <xsl:value-of select="."/>   </xsl:attribute>  </xsl:for-each>   <xsl:for-each select="NAME/child::*">    <xsl:copy-of select="."/>   </xsl:for-each>   <xsl:for-each select="NAME/following-sibling::*">    <xsl:copy-of select="." />   </xsl:for-each>  </xsl:copy> </xsl:template> </xsl:stylesheet> ½«Æäת»»³ÉÄܹ»½âÎöµÄXML: <?xml version="1.0" encoding="UTF-8"?> <ROOT>  <PAYOR bankID="001">   <PRNAME>BANK</PRNAME>   <PENAME>CUST</PENAME>   <TRN>2222</TRN>   <BREF>3333</BREF>   <DTM097>4444</DTM097>   <DTM107>1111</DTM107>   <TTLAMT>300</TTLAMT>   <TRNCNT>2</TRNCNT>  </PAYOR>  <ENT bank="001">   <FIRST-NAME>AAA</FIRST-NAME>   <LAST-NAME>AAA</LAST-NAME>   <ACNUM>001001001</ACNUM>   <CR>100</CR>   <CREF>1111</CREF>   <DTM109>1111</DTM109>  </ENT>  <ENT bank="001">   <FIRST-NAME>BBB</FIRST-NAME>   <LAST-NAME>BBB</LAST-NAME>   <ACNUM>002002002</ACNUM>   <CR>200</CR>   <CREF>1111</CREF>   <DTM109>1111</DTM109>  </ENT> </ROOT> È»ºóÔÙÐд¦Àí¾ÍºÃÁË¡£