W jakim typie danych mają być przechowywane dane XML: VARCHAR (MAX) lub XML

Definiuję schemat nowego zestawu zasobów za pomocą SQL Server 2008 ... W takim przypadku każdy rekord ( np. Wiersz ) będzie musiał przechowywać fragmenty XML. Od czasu do czasu; chociaż nie często; Będę musiał zapytać XML, aby znaleźć wartości elementu i atrybutu. Gdybym pozostawił to moim własnym pomysłom, zwykle używałbym typu danych xml, chociaż przekonano mnie, że jest to spowodowane problemami. To prowadzi mnie do moich pytań.

Biorąc pod uwagę ten scenariusz, jakie czynniki powinny być brane pod uwagę, że gdy stara się zdecydować między przechowywania XML w xml kolumny vs. varchar (MAX) kolumna

Jeśli to pomaga… oto kilka dodatkowych szczegółów:

Nie podjęto żadnej decyzji dotyczącej użycia schematów dla tych fragmentów ( np. XSD )
Rozmiary fragmentów będą wahać się od małych do bardzo dużych
Wszystkie XML będą dobrze sformułowane
W ciągu dnia zgromadzonych zostanie do ~ 10 000 fragmentów z obsługą zapytań online potrzebnych przez ~ 3 miesiące
Kwerendy względem XML będą się pojawiać przez cały dzień, ale powinny pozostać lekkie z kilkoma równoległymi zapytaniami tego typu

— JoeGeeky
źródło

Typ xml nie gwarantuje zachowania dokładnej formy oryginalnego xml, jeśli istnieje wymóg niezmienności dokumentu, wówczas jedyną opcją jest nvarchar (max).

— MartinC

@MartinC Jeśli fragment jest już dobrze uformowany, jaka zmiana może nastąpić? Wierzę ci, po prostu tego wcześniej nie słyszałem ... Czy możesz wskazać mi więcej szczegółów?

— JoeGeeky

Przykładem <foo></foo>będą puste tagi<foo />

— gbn

@gdn Ahhh, ok ... to nie zmienia znaczenia, więc nie mam nic przeciwko.

— JoeGeeky

Odpowiedzi:

Jeśli zapytania dotyczące XML będą się pojawiać dzięki możliwościom xml serwera sql, to użyj typu XML do przechowywania xml, aby uniknąć rzutowania

pamiętaj, że typ XML może być przechowywany nieco wolniej z powodu sprawdzania poprawności xml, ale podstawowym typem XML jest zwykły varbinary (maks.)

— Oleg Dok
źródło

Dane podstawowe nie są VARBINARY(MAX). Jest to zoptymalizowany format, co oznacza, że nawet jeśli nie zamierzasz go wyszukiwać, nadal powinieneś używać XMLtypu danych.

— Solomon Rutzky

jakie czynniki powinienem wziąć pod uwagę, próbując wybrać między przechowywaniem XML w xmlkolumnie a varchar(MAX)kolumną

Czynniki są następujące:

Ten XMLtyp jest możliwy do zapytania / analizowania za pomocą wyrażeń XQuery, w tym możliwość korzystania z instrukcji FLWOR i iteracji
Dane w XMLzmiennych i kolumnach można modyfikować bezpośrednio za pomocą wyrażeń XQuery za pośrednictwem XML DML .
XMLdane są przechowywane jako UTF-16 LE (Little Endian), więc VARCHAR(MAX)byłby to zły wybór, ponieważ mógłby spowodować utratę danych. Stąd prawdziwa decyzja powinna być pomiędzy, XMLa NVARCHAR(MAX)biorąc pod uwagę, że NCHAR/ NVARCHARto także UTF-16 LE.
XMLdane mogą być sprawdzane pod kątem XSD / XML SCHEMA COLLECTION. Sprawdzanie poprawności (poza zapewnieniem poprawności) nie jest wykonywane, jeśli nie określono kolekcji schematów XML, ale ta opcja nie jest dostępna podczas używania NVARCHAR(MAX).
Jedną z głównych zalet tego typu XML jest to, że jest przechowywany w wysoce zoptymalizowanym formacie (innym VARBINARY(MAX)niż podano w odpowiedzi @ Olega), który nie przechowuje dokładnej reprezentacji ciągu, którą widzisz, ale zamiast tego ma słownik nazw elementów i atrybutów oraz odwołuje się im przez ich identyfikator. Usuwa również białe znaki. Spróbuj wykonać następujące czynności:
```
DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';

DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);

SELECT DATALENGTH(@Test1) AS [XmlBytes],
       LEN(@String1) AS [StringCharacters],
       DATALENGTH(@String1) AS [StringBytes];

SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
<TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';

SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);

SELECT DATALENGTH(@Test1) AS [XmlBytes],
       LEN(@String1) AS [StringCharacters],
       DATALENGTH(@String1) AS [StringBytes];
```
Zwroty:
```
XmlBytes   StringCharacters   StringBytes
56         53                 106

XmlBytes   StringCharacters   StringBytes
84         133                266
```
Jak widać w powyższym przykładzie wyjściowym, dodanie czterech elementów (# 3, 4, 5 i 6) dodało 80 znaków (stąd 80 bajtów, jeśli jest używane VARCHAR) i 160 bajtów do NVARCHARzmiennej. Ale to tylko dodaje 28 bajtów do zmiennej XML, który jest mniej niż dodanej VARCHAR(tylko w przypadku, gdy ktoś jechał przemawiają za VARCHARponad XMLponieważ XMLjest UTF-16, który jest [przede wszystkim] dwubajtowych). Ta optymalizacja pozwala zaoszczędzić mnóstwo miejsca i sama w sobie jest wystarczającym powodem do użycia XMLtypu danych.

— Solomon Rutzky
źródło