Zabawa staje się trochę bardziej skomplikowana. W T-SQLu istnieje możliwość generowania danych w formacie XML i możemy ten format wykorzystać, by rozdzielić wartości w polu tekstowym do osobnych wierszy.

Kroki w tym działaniu mamy dwa:

1. Wygenerowanie XMLa, który będzie miał rozbite informacje o księgowaniach statusu.
2. Przedstawienie danych z XMLa z powrotem w formie tabeli.

W tej sytuacji trzeba wykorzystać klauzulę FOR XML PATH (), która odpowiada za generowanie struktury XML z zapytania SQL. Podstawową zasadą wykorzystania jest dopisanie jej po sekcji from z informacją o nazwie węzła głównego. Jeżeli chcemy tworzyć kolejne węzły i ich zagłębienia, to tworzymy w środku podzapytanie, które również korzysta z tej samej klauzuli.

DECLARE @XML XML =
		 (
			 SELECT TOP 1
			 O1.OrderNr as '@OrdeNr',
			 (
				SELECT
				OrderGrpNr,
				CAST(('<ProdStatus><Value>'
					+
						REPLACE(O2.ProductionStatusHistory,';' ,'</Value></ProdStatus><ProdStatus><Value>')
					+
					'</Value></ProdStatus>') as XML) ProductionStatuses
				FROM OrderGroups O2
				WHERE O2.OrderNr = O1.OrderNr 
				FOR XML PATH ('OrderGroups'),type
			 )
			 FROM OrderGroups O1
			 WHERE O1.OrderNr = 100
			 FOR XML PATH ('Order')
			)

			SELECT @XML

Ważnym elementem zapytania jest sekcja ProductionStatuses, która rozbija ciąg znaków z historią statusów poprzez zastosowanie funkcji REPLACE. Wymieniany jest separator na początki oraz rozpoczęcia poszczególnych elementów węzła. Dodatkowo dopisałem do każdego statusu produkcji numer zlecenia oraz grupy w formie atrybutu. Wszystko to zwieńczone jest funkcją CAST, która konwertuje ten podmieniony ciąg do formatu XML.

Wynikiem tego zapytania jest poniższa struktura:

<OrderGroupLists>
  <OrderGroup>
    <ProductionStatuses>
      <ProdStatus OrderNr="100" GrpNr="1">
        <Value>A=2020-05-20 15:38:30 </Value>
      </ProdStatus>
      <ProdStatus OrderNr="100" GrpNr="1">
        <Value>B=2020-05-21 10:12:30</Value>
      </ProdStatus>
      <ProdStatus OrderNr="100" GrpNr="1">
        <Value>C=2020-05-22 20:30:15</Value>
      </ProdStatus>
    </ProductionStatuses>
  </OrderGroup>
  <OrderGroup>
    <ProductionStatuses>
      <ProdStatus OrderNr="100" GrpNr="2">
        <Value>A=2020-05-20 15:50:30 </Value>
      </ProdStatus>
      <ProdStatus OrderNr="100" GrpNr="2">
        <Value>B=2020-05-21 10:35:30</Value>
      </ProdStatus>
    </ProductionStatuses>
  </OrderGroup>
  <OrderGroup>
    <ProductionStatuses>
      <ProdStatus OrderNr="100" GrpNr="3">
        <Value>A=2020-05-21 08:45:10 </Value>
      </ProdStatus>
      <ProdStatus OrderNr="100" GrpNr="3">
        <Value>B=2020-05-22 10:12:30</Value>
      </ProdStatus>
    </ProductionStatuses>
  </OrderGroup>
</OrderGroupLists>

Teraz musimy wrócić do oczekiwanej formy tabelarycznej. Z pomocą przychodzi nam metoda nodes, którą można wykorzystać dla zdefiniowanego w zmiennej XMLa jako źródła danych. Poniżej zapytanie, które wygląda następująco:

SELECT 
    XMLGroupProductionStatuses.value('(./@OrderNr)[1]', 'decimal(18,6)') AS 'OrderNr',
	XMLGroupProductionStatuses.value('(./@GrpNr)[1]', 'decimal(18,6)') AS 'OrderGrpNr',
	XMLGroupProductionStatuses.value('(./Value)[1]', 'nvarchar(100)') AS 'ProdStatus'
FROM 
    @XML.nodes('/OrderGroupLists/OrderGroup/ProductionStatuses/ProdStatus') as ref(XMLGroupProductionStatuses)

Jako tabelę wskazujemy zadeklarowanego w zmiennej XMLa. Za pomocą metody nodes definiujemy miejsce strukturze pliku, z którego chcemy zebrać dane. W tym przypadku wskazuję węzeł ProductionStatuses, który zawiera elementy ProdStatus – to w nich mamy dostępne informacje o wszystkich księgowaniach statusów.

W sekcji select wybieramy dane z elementów, które nas interesują. Zastosowanie kropki oznacza, że operujemy na węźle, który mamy wybrany w sekcji from. Istnieje możliwość wskazania ścieżki do innego węzła w XMLu, jeżeli potrzebujemy takie dane uzyskać.

Zastosowanie [1] po określeniu lokalizacji oznacza, że chcę wydobyć pierwszy element z danego zbioru (węzła). Forma i działanie jest identyczne jak w wielu językach programowania i operacji na tablicach.

Na końcu podajemy jeszcze w jakim typie danych chcemy zwrócić oczekiwaną wartość. Efektem takiego zapytania jest rezultat zaprezentowany poniżej:

Na koniec zróbmy jeszcze drobną korektę, która da nam rezultat końcowy:

	SELECT 
    	XMLGroupProductionStatuses.value('(./@OrderNr)[1]', 'decimal(18,6)') AS 'OrderNr',
		XMLGroupProductionStatuses.value('(./@GrpNr)[1]', 'decimal(18,6)') AS 'OrderGrpNr',
		SUBSTRING(XMLGroupProductionStatuses.value('(./Value)[1]', 'nvarchar(1000)'),1,1) AS 'ProdStatus',
		CAST(SUBSTRING(XMLGroupProductionStatuses.value('(./Value)[1]', 'nvarchar(100)'),3,LEN(XMLGroupProductionStatuses.value('(./Value)[1]', 'nvarchar(1000)'))-2) as datetime) as 'StatusBookings'
	FROM 
    	@XML.nodes('/OrderGroupLists/OrderGroup/ProductionStatuses/ProdStatus') as ref(XMLGroupProductionStatuses)

Podobał Ci się ten wpis? Zapraszam do mojej listy mailingowej by otrzymać więcej dodatkowych materiałów oraz być na bieżąco z nowościami!