Reads the XML attributes:
===================================================================
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='<ISKeyValueList version="1.00"><Item type="varchar(20)" key="VehicleSecurityPending">false</Item><Item type="Integer" key="TermInMonths">36</Item></ISKeyValueList>';
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT *
FROM OPENXML (@idoc, '/ISKeyValueList/Item',0)
WITH ([key] varchar(10),
[type] varchar(20)
);
Reads the Internal tags
====================================================================
With XMLAccount
As
(
Select
Pk,
XMLUserData = convert(xml, UserData)
From [finPOWERConnect_Oxford_PRE].[dbo].[Account]
),
Base
As
(
SELECT
pk,
c.value('@key', 'varchar(max)') AS [Key],
c.query('.') AS Item
FROM XMLAccount s
outer apply s.XMLUserData.nodes('ISKeyValueList/Item') as m(c)
)
select
top 100
pk,
TermInMonths = Item.value('(Item)[1]', 'varchar(100)')
from Base s
outer apply s.Item.nodes('/Item') as m(c)
where [key] = 'DecisionInitiator'
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='<ISKeyValueList version="1.00"><Item type="varchar(20)" key="VehicleSecurityPending">false</Item><Item type="Integer" key="TermInMonths">36</Item></ISKeyValueList>';
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT *
FROM OPENXML (@idoc, '/ISKeyValueList/Item',0)
WITH ([key] varchar(10),
[type] varchar(20)
);