如何在sql server中提取xml数据 [英] How do I extract xml data in sql server

问题描述

<USAGE>
 <Customer>ABC</Customer>
 <Age>38</Age>
 <Mobile>
   <Model>Nokia</Model>
   <Price>100</Price>
  </Mobile>
 <Mobile>
   <Model>Samsung</Model>
   <Price>300</Price>
  </Mobile>
</USAGE>

需要此格式的输出

Need the output in this format

Customer  Age  Mobile    Price
ABC       38    Nokia     100
ABC       38    Samsung   300

我将xml传递给xml变量。

I am passing the xml to xml variable.

SELECT 
					[xmlTable].[Customer].value('/Customer', 'varchar(100)') AS '[Customer]',
					[xmlTable].[Age].value('/Age','varchar(100)') AS '[Age]',
					(SELECT 
							[CustTable].Model.value('.','varchar(100)') AS 'Model'
						FROM @tallyXML.nodes('Mobile/Model') AS [CustTable](Model)),
					(SELECT 
							[CustTable].Price.value('.','varchar(100)') AS 'Price'
						FROM @tallyXML.nodes('Mobile/Price') AS [CustTable](Price))
			FROM @tallyXML.nodes('USAGE') AS [xmlTable])

请指导我如何获得所需格式的结果。还有另一种方法sp_xml_preparedocument但我不知道如何获得相同的格式。



谢谢

Please guide me how can I get the result in desired format. There is another way sp_xml_preparedocument but I am not aware how to get the same format in that also.

Thanks

推荐答案

尝试使用类似下面的示例：

Have a try with something like the following example:

declare @tallyXML as xml

set @tallyXML = '<USAGE>
 <Customer>ABC</Customer>
 <Age>38</Age>
 <Mobile>
   <Model>Nokia</Model>
   <Price>100</Price>
  </Mobile>
 <Mobile>
   <Model>Samsung</Model>
   <Price>300</Price>
  </Mobile>
</USAGE>'

SELECT CustTable.Mobile.value('../Customer[1]','varchar(100)') AS 'Customer',
       CustTable.Mobile.value('../Age[1]','varchar(100)') AS 'Age',
		CustTable.Mobile.value('./Model[1]','varchar(100)') AS 'Model',
		CustTable.Mobile.value('./Price[1]','varchar(100)') AS 'Price'
FROM @tallyXML.nodes('USAGE/Mobile')  as CustTable(Mobile)

这篇关于如何在sql server中提取xml数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！