Wybierz wartości z pola XML w SQL Server 2008
Po prostu patrząc na moje pole XML, moje wiersze wyglądają tak:
<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 jako tabelę jak w
Jon | Johnson
Kathy| Carter
Bob | Burns
Jakie zapytanie tego dokona?
9 answers
Biorąc pod uwagę, że pole XML ma 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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-05-22 18:39:47
Biorąc pod uwagę, że dane XML pochodzą z tabeli "table" i są przechowywane w kolumnie "field": użyj metod xml , wyodrębnij wartości z xml.value()
, węzły projektu z xml.nodes()
, użycie CROSS APPLY
aby dołą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 apply
, jeśli każde pole zawiera dokładnie jeden element 'person'. Jeśli XML jest zmienną Wybierz FROM @variable.nodes(...)
i nie potrzebujesz cross apply
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-16 04:41:01
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 i przechowuję XML w kolumnie SourceKeys w tabeli o nazwie DeleteBatch:
<k>1</k>
<k>2</k>
<k>3</k>
Utwórz tabelę i uzupeł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, aby wybrać klucze z XML:
SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
CROSS APPLY SourceKeys.nodes('/k') t(p)
Oto wyniki zapytania...
ExecutionKey Key 1 1 1 2 1 3 2 100 2 101
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-21 15:21:38
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-24 19:59:33
Kurczę. To był naprawdę przydatny wątek do odkrycia.
Nadal uważałem niektóre z tych sugestii za mylące. Ilekroć użyłem value
z [1]
w łańcuchu, pobierało tylko pierwszą wartość. A niektóre sugestie zalecają użycie cross apply
, które (w moich testach) po prostu przywróciło zbyt dużo danych.
Oto mój prosty przykład jak utworzyć obiekt xml
, a następnie odczytać jego wartości do 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 wyjście:
To dziwaczna składnia, ale z przyzwoitym przykładem, łatwo jest dodać do własnych funkcji serwera SQL.
A propos, oto poprawna odpowiedź na to pytanie.
Zakładając, że masz swoje dane xml w zmiennej @xml
typu xml
(jak pokazałem w moim przykładzie powyżej), oto jak zwrócisz 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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-08 10:13:45
Jeśli jesteś w stanie zawinąć swój XML w element główny-powiedzmy, że następujące rozwiązanie jest Twoje:
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-21 12:29:24
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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-24 06:52:11
/* Ten przykład wykorzystuje zmienną 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('/*')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-15 17:04:40
MSSQL używa regularnych 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ą
- . Zaznacza bieżący węzeł
- .. Zaznacza rodzica bieżącego węzła
- @ wybiera atrybuty
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-09-18 16:16:58