Gerar XML à partir de Coluna NText

[Cassiano Sanches 0]

Olá pessoal,

 

É o seguinte.

 

Eu tenho uma tabela que tem várias colunas e uma delas é do tipo ntext.

 

A coluna ntext contém dados no formato xml.

 

Eu queria fazer uma quey que gerasse um arquivo xml pra mim à partir do conteúdo da coluna ntext.

 

só para exemplificar.

 

<?xml version="1.0" encoding="utf-8"?><UnitInformation><section name="Unit Information"><setting name="AccountId" value="P5" /><setting name="action" value="" /><setting name="Complete" value="1" /><setting name="Conversion" value="0" /><setting name="CustItemNumber" value="Q6504A" /><setting name="CustomerOrder" value="380435" /><setting name="DataCollectionPoint" value="" /><setting name="Description" value="LaserJet 3050" /><setting name="EndDate" value="" /><setting name="EndTime" value="" /><setting name="eventName" value="" /><setting name="Item" value="Q6504A#696" /><setting name="ItemGroup" value="%%" /><setting name="MfgArea" value="LJ20" /><setting name="MfgCenter" value="BRSL" /><setting name="Note1" value="Indzstria Brasileira" /><setting name="Note2" value="Brazil - PT localization" /><setting name="Option" value="696" /><setting name="PartNumber" value="Q6504A" /><setting name="Product" value="Q6504A" /><setting name="ProductSN" value="BRRK342734" /><setting name="Resource" value="" /><setting name="Revision" value="A" /><setting name="RouterName" value="" /><setting name="RouterRevision" value="" /><setting name="SfcNumber" value="Q6504A-BRRK342734" /><setting name="ShopOrder" value="380435" /><setting name="Site" value="BRSL" /><setting name="StartDate" value="" /><setting name="StartTime" value="" /><setting name="StatusFlag" value="" /><setting name="Step" value="90" /><setting name="TestResults" value="1" /><setting name="Transaction" value="P5-FALCON" /><setting name="TxnNumber" value="" /><setting name="TxnType" value="" /><setting name="UPC" value="882780311848" /><setting name="Value10" value="" /><setting name="Value6" value="1200" /><setting name="Value7" value="" /><setting name="Value8" value="50/60Hz" /><setting name="Value9" value="" /><setting name="Voltage" value="110" /><setting name="VoltageText" value="110-127V" /><setting name="Workstation" value="lhipot2001" /><setting name="7121-8285" value="" /><setting name="0515-4679" value="" /><setting name="0515-4801" value="" /><setting name="5969-9497" value="" /><setting name="Q6504-00002" value="Q6504/M00002" /><setting name="Q7844A" value="HG60NXQ" /><setting name="5011-2730" value="" /><setting name="0380B001AA" value="CNBS573161" /><setting name="5851-2476" value="" /><setting name="Q6504-00026" value="Q6504/M00026" /><setting name="5851-2577" value="" /><setting name="Q2663-60011" value="BUBBNFA6" /><setting name="Q6502-00001" value="" /><setting name="Q6502-00003" value="" /><setting name="MKTNG-00491" value="" /><setting name="RM1-3414" value="" /><setting name="5851-2967" value="" /><setting name="5011-2785" value="" /><setting name="0960-2401" value="" /><setting name="AntelopeFI.adt" value="20070814 142356" /><setting name="PowerOffMsg" value="None" /><setting name="FalconRev" value="X15.3" /><setting name="FalconFlag1" value="0" /><setting name="FalconFlag2" value="0" /><setting name="FalconFlag3" value="0" /><setting name="FalconFlag4" value="0" /><setting name="OS" value="XP" /><setting name="TCDNumber" value="BPick101320052843_R13" /><setting name="CURRENTMODEL" value="MODELB" /><setting name="VALIDOPTION" value="YES" /><setting name="PAPERSIZE" value="A4" /><setting name="TestPaper" value="A4" /><setting name="Generic" value="OFF" /><setting name="OVERLAY" value="NO" /><setting name="SymSet" value="NOTSET" /><setting name="DefAsiaMediaName" value="Plain" /><setting name="ESTAR" value="NO" /><setting name="TempPageCount" value="0" /></section><section name="Build Info"><setting name="AdtPath" value="C:\FalconX\Antelope\" /><setting name="Pictures" value="C:\FalconX\Antelope\pictures" /><setting name="Printouts" value="C:\FalconX\Antelope\printout" /><setting name="PSM Files" value="C:\FalconX\Antelope\Psmfiles" /><setting name="ScriptFiles" value="C:\FalconX\Antelope\scripts" /></section><section name="Segment List"><setting name="Count" value="0" /></section><section name="TestResults"><setting name="FalconStart" value="1187112236" /><setting name="FINISHED" value="1" /><setting name="Power Off/Safety" value="123" /><setting name="Update Test Log with TCD number" value="123" /><setting name="Setup Test Variables" value="124" /><setting name="Start Here" value="124" /><setting name="Inspect Cosmetics - SITE NOT {"PDE","METZ"}" value="124" /><setting name="Inspect Screws - SITE NOT {"PDE","METZ"}" value="124" /><setting name="Verify IO Plate not Shorting Resistor - SITE NOT {"PDE","METZ"}" value="125" /><setting name="Install Formatter Panel - SITE NOT {"PDE","METZ"}" value="125" /><setting name="Inspect Printouts" value="125" /><setting name="Inspect/Verify Jewel" value="125" /><setting name="Inspect EP Area" value="125" /><setting name="Verify Chassis Label - PARTNUMBER NOT {"Q6510A","Q6510V","Q6510Q","Q6510M"}" value="125" /><setting name="Verify Chassis Label 110V" value="125" /><setting name="Install or Verify "No Staples" Label" value="126" /><setting name="Install or Verify Customer Setup Sheet" value="126" /><setting name="Inspection Complete" value="126" /><setting name="End Of File" value="126" /></section><section name="Test Log"><setting name="FalconRev" value="X15.3" /><setting name="OS" value="XP" /><setting name="TotalFalconXRestarts" value="1" /><setting name="TotalFalconXRetries" value="0" /><setting name="FalconXRetries1" value="0" /><setting name="FI_TCDNumber" value="BPick101320052843_R13" /><setting name="PrtCommXRev" value="0.15.0.11" /></section></UnitInformation>

isso é parte do conteúdo da minha coluna ntext

 

 

O que eu gostaria.

 

Gerar um arquivo xml com o retorno de uma query dessa COLUNA e se possível escolher quais partes do xml eu quero retornar.

 

Valeu.

 

 

Já tentei fazer select usando for XML RAW , XMLDATA

for XML Auto e tudo mais..

[Cassiano Sanches 0]

DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot">   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">	  <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>	  <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>   </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">	  <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>   </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT	*FROM	   OPENXML (@idoc, '/ROOT/Customer',1)			WITH (CustomerID  varchar(10),				  ContactName varchar(20))

pronto..

 

fonte: http://msdn2.microsoft.com/en-us/library/ms186918.aspx