Consultar colunas XML com XQuery em T-SQL
O exemplo seguinte pode ser executado aqui CREATE TABLE propertyconf ( 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) ...
