Просто глядя на мое поле XML, мои строки выглядят так:
<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>
Обратите внимание, что это три строки в моей таблице.
Я хотел бы вернуть результат SQL в виде таблицы, как в
Jon | Johnson
Kathy| Carter
Bob | Burns
Какой запрос выполнит это?
Учитывая, что поле XML называется "xmlField"...
SELECT
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]
Учитывая, что данные XML поступают из таблицы "table" и сохраняются в поле "column": используйте XML-методы, извлеките значения с xml.value(), узлы проекта с xml.nodes(), используйте CROSS APPLY, чтобы присоединиться:
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)
Вы можете вырезать nodes() и CROSS APPLY, если каждое поле содержит ровно один элемент "человек". Если XML является переменной, вы выбираете FROM @variable.nodes(...), и вам не нужен CROSS APPLY.
Это сообщение было полезно для решения моей проблемы, которая имеет немного другой формат XML... мой XML содержит список ключей, подобных приведенному ниже примеру, и я храню XML в столбце SourceKeys в таблице с именем DeleteBatch:
<k>1</k>
<k>2</k>
<k>3</k>
Создайте таблицу и заполните ее некоторыми данными:
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))
Здесь мой SQL для выбора ключей из XML:
SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
CROSS APPLY SourceKeys.nodes('/k') t(p)
Здесь результаты запроса...
ExecutionKey Key 1 1 1 2 1 3 2 100 2 101
Это может ответить на ваш вопрос:
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
Blimey. Это была действительно полезная тема для обнаружения.
Я все-таки нашел некоторые из этих предложений запутанными. Всякий раз, когда я использовал value с [1] в строке, он получал только первое значение. И некоторые предложения, рекомендованные с помощью cross apply, которые (в моих тестах) просто вернули слишком много данных.
Итак, вот мой простой пример того, как вы создадите объект xml, а затем зачитайте его значения в таблице.
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)
И вот вывод:
Это странный синтаксис, но с достойным примером его легко добавить к вашим собственным функциям SQL Server.
Говоря об этом, здесь правильный ответ на этот вопрос.
Предполагая, что ваши xml-данные в переменной @xml типа xml (как показано в моем примере выше), вот как вы вернете три строки данных из xml, указанных в вопросе:
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)
Если вы можете обернуть свой XML в корневой элемент - скажем, тогда ваше решение:
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)
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]
/* В этом примере используется XML-переменная со схемой */
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('/*')