>

3 개의 열이있는 아래 XML 데이터를 SQL Server 테이블로 가져 오는 방법?

<dataset> 
 <metadata>
  <item name="NAME_LAST" type="xs:string" length="62" /> 
  <item name="NAME_FIRST" type="xs:string" length="62" /> 
  <item name="NAME_MIDDLE" type="xs:string" length="32" />
 </metadata>
<data>
<row>
 <value>SMITH</value> 
 <value>MARY</value> 
 <value>N</value> 
</row>
<row>
 <value>SMITH2</value> 
 <value>MARY2</value> 
 <value>N2</value> 
</row>
</data>
</dataset>


  • 답변 # 1

    이것을 시도하십시오 :

    DECLARE @input XML = '<dataset> 
     <metadata>
      <item name="NAME_LAST" type="xs:string" length="62" /> 
      <item name="NAME_FIRST" type="xs:string" length="62" /> 
      <item name="NAME_MIDDLE" type="xs:string" length="32" />
     </metadata>
    <data>
    <row>
     <value>SMITH</value> 
     <value>MARY</value> 
     <value>N</value> 
    </row>
    <row>
     <value>SMITH2</value> 
     <value>MARY2</value> 
     <value>N2</value> 
    </row>
    </data>
    </dataset>'
    INSERT INTO dbo.YourTable(ColName, ColFirstName, ColOther)
       SELECT
          Name = XCol.value('(value)[1]','varchar(25)'),
          FirstName = XCol.value('(value)[2]','varchar(25)'),
          OtherValue = XCol.value('(value)[3]','varchar(25)')
       FROM 
          @input.nodes('/dataset/data/row') AS XTbl(XCol)
    
    

  • 답변 # 2

    Insert XML Data into sql Server table
    Declare @retValue1 varchar(50);
    Declare @XmlStr XML;
    SET @XmlStr='<Customers>
     <customer>
        <ID>111589</ID>
        <FirstName>name1</FirstName>
        <LastName>Lname1</LastName>
        <Company>ABC</Company>
      </customer>
      <customer>
        <ID>12345</ID>
        <FirstName>name2</FirstName>
        <LastName>Lname2</LastName>
        <Company>ABC</Company>
      </customer>
      <customer>
        <ID>14567</ID>
        <FirstName>name3</FirstName>
        <LastName>Lname3</LastName>
        <Company>DEF</Company>
      </customer>
    </Customers>';
    @retValue='Failed';
    INSERT INTO  [test_xmlinsert](
    [id],
    [firstName],
    [lastName],
    [company]
    )
    SELECT
    COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
    [Table].[Column].value('FirstName [1]', 'varchar(50)') as ' FirstName ',
    [Table].[Column].value(' LastName[1]', 'varchar(50)') as ' LastName',
    [Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
     FROM @xmlData.nodes('/ Customers / customer') as [Table]([Column])
    IF(@@ROWCOUNT > 0 )
      SET @retValue='SUCCESS';
    
    

관련 자료

  • 이전 python - 플라스크 - restful api 및 socketio 서버
  • 다음 자이 썬없이 브라우저 내에서 파이썬 코드 실행