Consultar colunas XML com XQuery em T-SQL
O exemplo seguinte pode ser executado aqui
(
id INT IDENTITY(1, 1) NOT NULL,
NAME VARCHAR(8) NULL,
document NVARCHAR(max) NULL
)
INSERT INTO propertyconf
SELECT 'Property',
(SELECT 'Street A' AS 'Street',
'My City' AS 'City'
FOR xml path('Address'), root('Property')) AS Doc
INSERT INTO propertyconf
VALUES ('Location',
'<Property><Location type="address"><Composition>street</Composition><Composition>city</Composition></Location></Property>'
)
SELECT prop.addr.value('Street[1]', 'nvarchar(512)') AS Street,
prop.addr.value('City[1]', 'nvarchar(512)') AS City
FROM (SELECT CONVERT(XML, document) AS c
FROM propertyconf
WHERE NAME = 'Property') AS t
OUTER apply c.nodes('/Property/Address') AS Prop (addr)
SELECT prop.loc.value('../@type', 'nvarchar(512)') AS LocationType,
prop.loc.value('.[1]', 'nvarchar(512)') AS Composition
FROM (SELECT CONVERT(XML, document) AS c
FROM propertyconf
WHERE NAME = 'Location') AS t
OUTER apply c.nodes('/Property/Location/Composition') AS Prop (loc)
Consultar colunas XML com XQuery em T-SQL
![Consultar colunas XML com XQuery em T-SQL]()
Reviewed by Silvia Pinhão Lopes
on
27.11.21
Rating: 5
