Как преобразовать вложенный XML в соответствующие таблицы?

Question

У меня есть сложный вложенный XML (сгенерированный из графа сущностей C#), например:

<Customers>
  <Customer>
    <Id>1</Id>
    <Number>12345</Number>
    <Addresses>
      <Address>
        <Id>100</Id>
        <Street>my street </street>
        <city>London</city>
      </Address>
      <Address>
        <Id>101</Id>
        <street>my street 2</street>
        <city>Berlin</city>
      </Address>
    </Addresses>
    <BankDetails>
      <BankDetail>
        <Id>222</Id>
        <Iban>DE8439834934939434333</Iban>
      </BankDetail>
      <BankDetail>
        <Id>228</Id>
        <Iban>UK1237921391239123213</Iban>
      </BankDetail>
    </BankDetails>
    <Orders>
      <Order>         
        <OrderLine>         
        </OrderLine>
      </Order>
    </Orders>
  </Customer>
</Customers>

Прежде чем сохранять вышеупомянутые данные XML в реальных таблицах, мне нужно сначала обработать их. По этой причине я создал соответствующие типы таблиц. У каждого из этих типов таблиц есть дополнительный столбец (guid as ROWGUID), поэтому, если я обрабатываю новые данные (еще не назначен первичный ключ), я генерирую уникальный ключ. Я использую этот столбец для сохранения целостности отношений между различными типами таблиц.

Каков синтаксис SQL для преобразования вышеуказанного вложенного XML в соответствующие им таблицы, учитывая, что дочерние записи должны ссылаться на сгенерированный родительский guid?

Answer 1

Попробуйте это так:

DECLARE @xml XML=
N'<Customers>
  <Customer>
    <Id>1</Id>
    <AccountNumber>12345</AccountNumber>
    <Addresses>
      <Address>
        <Id>100</Id>
        <street>my street&gt;</street>
        <city>London</city>
      </Address>
      <Address>
        <Id>101</Id>
        <street>my street&gt;</street>
        <city>Berlin</city>
      </Address>
    </Addresses>
    <BankDetails>
      <BankDetail>
        <Id>222</Id>
        <Iban>DE8439834934939434333</Iban>
      </BankDetail>
      <BankDetail>
        <Id>228</Id>
        <Iban>UK1237921391239123213</Iban>
      </BankDetail>
    </BankDetails>
    <Orders>
      <Order>
        <OrderLine />
      </Order>
    </Orders>
  </Customer>
</Customers>';

- Этот запрос создаст таблицу #tmpInsert со всеми данными

SELECT cust.value('Id[1]','int') AS CustomerID
      ,cust.value('AccountNumber[1]','int') AS CustomerAccountNumber
      ,addr.value('Id[1]','int') AS AddressId
      ,addr.value('street[1]','nvarchar(max)') AS AddressStreet
      ,addr.value('city[1]','nvarchar(max)') AS AddressCity
      ,bank.value('Id[1]','int') AS BankId
      ,bank.value('Iban[1]','nvarchar(max)') AS BankIban
      ,ord.value('OrderLine[1]','nvarchar(max)') AS OrderLine
INTO #tmpInsert
FROM @xml.nodes('/Customers/Customer') AS A(cust)
OUTER APPLY cust.nodes('Addresses/Address') AS B(addr)
OUTER APPLY cust.nodes('BankDetails/BankDetail') AS C(bank)
OUTER APPLY cust.nodes('Orders/Order') AS D(ord);

- Здесь вы можете проверить содержание

SELECT * FROM #tmpInsert;

--Clean-Up

GO
DROP TABLE #tmpInsert

Как только у вас есть все ваши данные в таблице, вы можете использовать простой DISTINCT, GROUP BY, если нужно ROW_NUMBER() OVER(PARTITION BY ...) выбрать каждый набор отдельно для правильной вставки.