尝试将 XML 文件导入主/详细信息 SQL 表

Question

尝试使用SQL存储的Proc.将XML导入表。我希望将会员信息部分email_address，first_name，last_name插入到master中。 然后使用主密钥插入到"实例"表中的实例。然后使用主密钥插入到"事务"表中的事务。 附上示例 XML。

    <ArrayOfMemberInformation>
  <MemberInformation Member_Id="111111" Constituent_Id="" Last_Updated="2012-02-01T14:10:57" Last_Updated_By_Constituent_Id="" Last_Updated_By_Name="Jon doe">
    <Column Name="email_address" Value="[e-mail]" />
    <Column Name="first_name" Value="Jon " />
    <Column Name="last_name" Value="Doe" />
    <Column Name="email_address" Value="[e-mail]" />
    <Column Name="first_name" Value="Jon " />
    <Column Name="last_name" Value="Doe" />
    <Instances>
      <Instance Instance_Id="333333-4545-8C4543-6676756">
        <Column Name="ac member" Value="Y" />
         <Column Name="ac gradyear" Value="1833" />
        <Column Name="ac line1" Value="Doe  FAMILY" />
        <Column Name="ac line2" Value="'69  '92    " />
        <Column Name="ac line3" Value="'95  '99" />
        <Column Name="ac line4" Value="" />
        <Column Name="ac line5" Value="" />
         <Column Name="receiptpreference" Value="" />
        <Transactions>
          <Transaction Transaction_Id="11111" IsSuccess="true" IsReviewed="true">
            <Column Name="amount" Value="5.0000" />
             <Column Name="billingname" Value="Jon doe" />
             <Column Name="achchecknum" Value="" />
             <Column Name="billingemail" Value="[e-mail]" />
            <Column Name="billingphone" Value="111-222-22222" />
            <Column Name="billingstreet1" Value="44 kel Ct" />
            <Column Name="billingstreet2" Value="" />
            <Column Name="billingcity" Value="city" />
            <Column Name="billingstate" Value="OH" />
            <Column Name="billingzip" Value="55555" />
            <Column Name="billingcountry" Value="US" />
            <TransactionItems>
              <TransactionItem I_Member_Id="222222" Type="Amt" Transaction_Item_Id="Amount-1111">
                <Column Name="total" Value="5.0000" />
              </TransactionItem>
            </TransactionItems>
            <LinkedTransaction>
              <TransactionId>0</TransactionId>
              <Amount>0</Amount>
              <ControlId>0</ControlId>
              <ControlType />
              <ControlName />
              <PaymentType />
              <TotalScheduledAmount>0</TotalScheduledAmount>
            </LinkedTransaction>
            <LedgerValues>
              <LedgerValue Name="ALLCOE " Value="PREP" />
            </LedgerValues>
          </Transaction>
        </Transactions>
      </Instance>
    </Instances>
  </MemberInformation>
</ArrayOfMemberInformation>

Answer 1

你的问题还很不清楚...

我能猜到的是，你想从这个XML中读取。我建议读到临时表中。然后，您可以继续处理表格数据。您可以为 ref-Id 添加列，将所有这些固定在临时表中，然后将数据转移到目标表中。

我不想为您完成所有工作，但以下内容将从 XML 中的任何位置读取至少一个值。这将使您有足够的能力自己找到所有其他值。尝试一下，然后提出一个更具体（新）的问题。

    DECLARE @xml XML=
    N'<ArrayOfMemberInformation>
      <MemberInformation Member_Id="111111" Constituent_Id="" Last_Updated="2012-02-01T14:10:57" Last_Updated_By_Constituent_Id="" Last_Updated_By_Name="Jon doe">
        <Column Name="email_address" Value="[e-mail]" />
        <Column Name="first_name" Value="Jon " />
        <Column Name="last_name" Value="Doe" />
        <Column Name="email_address" Value="[e-mail]" />
        <Column Name="first_name" Value="Jon " />
        <Column Name="last_name" Value="Doe" />
        <Instances>
          <Instance Instance_Id="333333-4545-8C4543-6676756">
            <Column Name="ac member" Value="Y" />
            <Column Name="ac gradyear" Value="1833" />
            <Column Name="ac line1" Value="Doe  FAMILY" />
            <Column Name="ac line2" Value="''69  ''92    " />
            <Column Name="ac line3" Value="''95  ''99" />
            <Column Name="ac line4" Value="" />
            <Column Name="ac line5" Value="" />
            <Column Name="receiptpreference" Value="" />
            <Transactions>
              <Transaction Transaction_Id="11111" IsSuccess="true" IsReviewed="true">
                <Column Name="amount" Value="5.0000" />
                <Column Name="billingname" Value="Jon doe" />
                <Column Name="achchecknum" Value="" />
                <Column Name="billingemail" Value="[e-mail]" />
                <Column Name="billingphone" Value="111-222-22222" />
                <Column Name="billingstreet1" Value="44 kel Ct" />
                <Column Name="billingstreet2" Value="" />
                <Column Name="billingcity" Value="city" />
                <Column Name="billingstate" Value="OH" />
                <Column Name="billingzip" Value="55555" />
                <Column Name="billingcountry" Value="US" />
                <TransactionItems>
                  <TransactionItem I_Member_Id="222222" Type="Amt" Transaction_Item_Id="Amount-1111">
                    <Column Name="total" Value="5.0000" />
                  </TransactionItem>
                </TransactionItems>
                <LinkedTransaction>
                  <TransactionId>0</TransactionId>
                  <Amount>0</Amount>
                  <ControlId>0</ControlId>
                  <ControlType />
                  <ControlName />
                  <PaymentType />
                  <TotalScheduledAmount>0</TotalScheduledAmount>
                </LinkedTransaction>
                <LedgerValues>
                  <LedgerValue Name="ALLCOE " Value="PREP" />
                </LedgerValues>
              </Transaction>
            </Transactions>
          </Instance>
        </Instances>
      </MemberInformation>
    </ArrayOfMemberInformation>';

-

-查询会将一些值切碎到临时表中：

SELECT mi.value('@Member_Id','int') Attribute_MemberId --attribute of MemberInformation
      ,mi.value('(Column[@Name="email_address"]/@Value)[1]','nvarchar(max)') PickThecolumnByName_eMail
      ,mi.value('(Column[@Name="first_name"]/@Value)[1]','nvarchar(max)') PickThecolumnByName_FirstName
      ,inst.value('@Instance_Id','nvarchar(max)') Instance_Id
      ,inst.value('(Column[@Name="ac member"]/@Value)[1]','nvarchar(max)') Instance_AcMember
      ,trns.value('@Transaction_Id','nvarchar(max)') Transaction_Id
      ,trns.value('(Column[@Name="amount"]/@Value)[1]','decimal(12,4)') Transaction_Amount
      ,trns.value('(LinkedTransaction/TransactionId)[1]','int') LikedTransactionId
INTO #stagingTable
FROM @xml.nodes('/ArrayOfMemberInformation/MemberInformation') A(mi)
OUTER APPLY A.mi.nodes('Instances/Instance') B(inst)
OUTER APPLY B.inst.nodes('Transactions/Transaction') C(trns);

-

-检查结果;

SELECT * FROM #stagingTable