Wybierz wartości z pola XML w SQL Server 2008


112

Patrząc na moje pole XML, moje wiersze wyglądają następująco:

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

Zauważ, że są to trzy wiersze w mojej tabeli.

Chciałbym zwrócić wynik SQL w postaci tabeli, jak w

Jon  | Johnson
Kathy| Carter
Bob  | Burns

Jakie zapytanie to umożliwi?


Czy nie ma sposobu, aby po prostu pobrać WSZYSTKIE elementy w xml? Musisz określić jeden po drugim? To szybko staje się nudne. Możesz zrobić "wybierz * z tabeli", wydaje się, że powinieneś być w stanie zrobić "wybierz xml. * Z xml" bez konieczności określania każdego elementu, który chcesz.
Keith Tyler

Odpowiedzi:


157

Biorąc pod uwagę, że pole XML nosi nazwę „xmlField” ...

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

16
Musisz użyć .nodes () i stosować krzyżowo, jeśli xmlField zawiera więcej niż jeden element <person>.
Remus Rusanu

SQL Server 2008 R2 Express, zwrócił mi ten błąd z Twoim rozwiązaniem The XQuery syntax '/function()' is not supported.:; Z drugiej strony @Remus Rusanu wydaje się to robić :)
RMiranda

2
Dziwaczny. Ta odpowiedź została przegłosowana 102 razy, ale ta odpowiedź zwraca tylko dane z pierwszego rekordu XML. I odnosi się do jakiegoś stołu [myTable] ... skąd to się wzięło ?!
Mike Gledhill

Próbowałem tego wiele razy i nigdy nie działało. Mój XML to <BAM><Type>Electrical</Type><BaIds><a:int>7330</a:int></BaIds></BAM>, mój wybór to select e.MessageData.value('(/BAM/Type)[1]', 'varchar(100)'). Próbowałem również select e.MessageData.value('(/BAM/Type/node())[1]', 'varchar(100)'), i '(//Type/node())[1]', '(./Type)[1]'i każda inna kombinacja mogę myśleć. Wszystko, co kiedykolwiek otrzymałem, to NULL.
JonathanPeel

1
@MikeGledhill zwraca dla mnie wartości z wielu rekordów XML. Jedyną nazwą tabeli, którą podaje OP, jest „mój stół” :)
Paul,

123

Biorąc pod uwagę, że dane XML pochodzą z tabeli „tabela” i są przechowywane w kolumnie „pole”: użyj rozszerzenia metod XML , wyodrębnij wartości za pomocą xml.value(), węzły projektu za pomocą xml.nodes(), użyj, CROSS APPLYaby połączyć:

SELECT 
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
    CROSS APPLY field.nodes('/person') t(p)

Możesz porzucić nodes() i, cross applyjeśli każde pole zawiera dokładnie jeden element „osoba”. Jeśli XML jest wybraną zmienną FROM @variable.nodes(...)i nie potrzebujesz rozszerzenia cross apply.


1
Zastanawiam się, jak skuteczna jest ta metoda i czy istnieje lepszy sposób. Połączenie CROSS APPLY z wynikami XPath wydaje się skutkować zapytaniem wymagającym dużej ilości zasobów.
redcalx

1
@thelocster: to nie różni się od zwykłego dostępu do danych. Techniki poprawy wydajności XML są dobrze udokumentowane. msdn.microsoft.com/en-us/library/ms345118%28SQL.90%29.aspx
Remus Rusanu

2
pamiętaj, że jeśli twój XML ma zdefiniowane przestrzenie nazw xmlns, musisz je zdefiniować w powyższym wyrażeniu XQuery (XPath). Przykład można znaleźć na stackoverflow.com/a/1302150/656010 .
Tom Wayson,

Nieco inne niż to, czego potrzebowałem, ale było to idealne rozwiązanie problemu, który miałem, który dotyczył wielu wierszy z kolumną XML - chciałem przejrzeć wiersze i wyciągnąć pola danych z kolumny XML i umieścić je w instrukcja wstawiania. Czyli 5 wierszy, każdy na 3 kolumny danych w polu XML = 15 wstawień, idealnie.
dan richardson

17

Ten post był pomocny w rozwiązaniu mojego problemu, który ma trochę inny format XML ... mój XML zawiera listę kluczy, jak w poniższym przykładzie, a ja przechowuję XML w kolumnie SourceKeys w tabeli o nazwie DeleteBatch:

<k>1</k>
<k>2</k>
<k>3</k>

Utwórz tabelę i wypełnij ją danymi:

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1, 
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2, 
    (CAST('<k>100</k><k>101</k>' AS XML))

Oto mój SQL do wybierania kluczy z XML:

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

Oto wyniki zapytania ...

Klucz ExecutionKey
1 1
1 2
1 3
2 100
2 101,

9

To może odpowiedzieć na twoje pytanie:

select cast(xmlField as xml) xmlField into tmp from (
select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField
union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'
union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'
) tb

SELECT
    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName
FROM tmp

drop table tmp

6

Cholera. To był naprawdę przydatny wątek do odkrycia.

Nadal uważam, że niektóre z tych sugestii są mylące. Za każdym razem, gdy użyłem znaku valuewith [1]w ciągu, pobierałbym tylko pierwszą wartość. I niektóre sugestie zalecały użycie, cross applyktóre (w moich testach) po prostu przywróciło zbyt dużo danych.

Oto mój prosty przykład, jak utworzyć xmlobiekt, a następnie odczytać jego wartości w tabeli.

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

A oto wynik:

wprowadź opis obrazu tutaj

Jest to dziwaczna składnia, ale z przyzwoitym przykładem wystarczy dodać do własnych funkcji SQL Server.

A skoro o tym mowa, oto poprawna odpowiedź na to pytanie.

Zakładając, że masz swoje dane xml w @xmlzmiennej typu xml(jak pokazano w moim przykładzie powyżej), oto jak możesz zwrócić trzy wiersze danych z xml cytowanego w pytaniu:

SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)

wprowadź opis obrazu tutaj


Nie rozumiem, jak to jest poprawna odpowiedź. OP prosi o zapytanie o kolumnę z tabeli, która jest typu XML, iw takim przypadku musisz albo użyć numeru [1]porządkowego indeksu, aby zmusić go do zwrócenia 1 wiersza, albo musisz zastosować krzyżowo kolumnę z, nodes()aby uzyskać struktura, która może mieć przeciwko niej działanie xpath. Twój kod nie przekłada się na taki scenariusz bez wielu modyfikacji. Używasz zmiennej, a nie kolumny tabeli. Nadużywasz również query()funkcji, która zwraca xml. np. możesz mieć tylkox.Rec.value('(./firstName)[1]', 'nvarchar(2000)') AS FirstName
Davos

3

Jeśli jesteś w stanie zawinąć swój XML w element główny - powiedz, że masz następujące rozwiązanie:

DECLARE @PersonsXml XML = '<persons><person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person></persons>'

SELECT  b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName
FROM @PersonsXml.nodes('/persons/person') AS a(b)

wprowadź opis obrazu tutaj


3

MSSQL używa zwykłych reguł XPath w następujący sposób:

  • nodename Wybiera wszystkie węzły o nazwie „nodename”
  • / Wybiera z węzła głównego
  • // Wybiera węzły w dokumencie z bieżącego węzła, które pasują do zaznaczenia bez względu na to, gdzie się znajdują
  • . Wybiera bieżący węzeł
  • .. Wybiera rodzica bieżącego węzła
  • @ Wybiera atrybuty

W3Schools


2
SELECT 
cast(xmlField as xml).value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
cast(xmlField as xml).value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

0

/ * W tym przykładzie użyto zmiennej XML ze schematem * /

IF EXISTS (SELECT * FROM sys.xml_schema_collections 
           WHERE name = 'OrderingAfternoonTea')
BEGIN
    DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea 
END
GO

CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS
N'<?xml version="1.0" encoding="UTF-16" ?>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     targetNamespace="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     elementFormDefault="qualified"
     version="0.10"
   > 
    <xsd:complexType name="AfternoonTeaOrderType">
       <xsd:sequence>
         <xsd:element name="potsOfTea" type="xsd:int"/>
         <xsd:element name="cakes" type="xsd:int"/>
         <xsd:element name="fruitedSconesWithCream" type="xsd:int"/>
         <xsd:element name="jams" type="xsd:string"/>
      </xsd:sequence>
      <xsd:attribute name="schemaVersion" type="xsd:long" use="required"/>
    </xsd:complexType>

    <xsd:element name="afternoonTeaOrder"
                 type="TFor2:AfternoonTeaOrderType"/>

  </xsd:schema>' ;
GO

DECLARE @potsOfTea int;
DECLARE @cakes int;
DECLARE @fruitedSconesWithCream int;
DECLARE @jams nvarchar(128);

DECLARE @RequestMsg NVARCHAR(2048);
DECLARE @RequestXml XML(dbo.OrderingAfternoonTea);

set @potsOfTea = 5;
set @cakes = 7;
set @fruitedSconesWithCream = 25;
set @jams = N'medlar jelly, quince and mulberry';

SELECT @RequestMsg = N'<?xml version="1.0" encoding="utf-16" ?>
<TFor2:afternoonTeaOrder schemaVersion="10"
    xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea">
    <TFor2:potsOfTea>' + CAST(@potsOfTea as NVARCHAR(20)) 
        + '</TFor2:potsOfTea>
    <TFor2:cakes>' + CAST(@cakes as NVARCHAR(20)) + '</TFor2:cakes>
    <TFor2:fruitedSconesWithCream>' 
        + CAST(@fruitedSconesWithCream as NVARCHAR(20))
        + '</TFor2:fruitedSconesWithCream>
    <TFor2:jams>' + @jams + '</TFor2:jams>
</TFor2:afternoonTeaOrder>';

SELECT @RequestXml  = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ;

with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea'
                    as tea)
select
    cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint )
        as schemaVersion,
    cast( x.Rec.query('./tea:potsOfTea')
               .value('.','nvarchar(20)') as bigint ) as potsOfTea,
    cast( x.Rec.query('./tea:cakes')
               .value('.','nvarchar(20)') as bigint )  as cakes,
    cast( x.Rec.query('./tea:fruitedSconesWithCream')
               .value('.','nvarchar(20)') as bigint ) 
      as fruitedSconesWithCream,
    x.Rec.query('./tea:jams').value('.','nvarchar(50)')  as jams
from @RequestXml.nodes('/tea:afternoonTeaOrder')  as x(Rec);

select @RequestXml.query('/*')
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.