处有
1:n数据，处
<Margin>又有
1:n数据
<MarginRevenue>。您需要通过
.nodes()两次使用
APPLY。
declare @xml xml = '<Margins> <Margin type="type1" currencyCode="currencyCode1"> <MarginRevenue>1.1</MarginRevenue> <MarginRevenue>1.2</MarginRevenue> <MarginRevenue>1.3</MarginRevenue> </Margin> <Margin type="type2" currencyCode="currencyCode2"> <MarginRevenue>1.4</MarginRevenue> <MarginRevenue>1.5</MarginRevenue> <MarginRevenue>1.6</MarginRevenue> </Margin> <Margin type="type3" currencyCode="currencyCode3"> <MarginRevenue>1.7</MarginRevenue> <MarginRevenue>1.8</MarginRevenue> <MarginRevenue>1.9</MarginRevenue> </Margin> </Margins>'SELECt [Margin_Type] = Marg.value('@type', 'varchar(100)') ,[Margin_currencyCode] = Marg.value('@currencyCode', 'varchar(100)'),[Revenue_Value] = Rev.value('text()[1]','decimal(15,5)') FROM@xml.nodes('Margins/Margin') AS A(Marg)OUTER APPLY Marg.nodes('MarginRevenue') B(Rev);结果
Type currencyCode Revenue_Value-------------------------------------type1 currencyCode1 1.10000type1 currencyCode1 1.20000type1 currencyCode1 1.30000type2 currencyCode2 1.40000type2 currencyCode2 1.50000type2 currencyCode2 1.60000type3 currencyCode3 1.70000type3 currencyCode3 1.80000type3 currencyCode3 1.90000
