SQL Server Performance Forum – Threads Archive
Shredding XML with nodes() method
I want to get the same output using "nodes() method" without using xml procedures (sp-xml_preparedocument and sp-xml_removedocument)….I tried using BOL help without success…<br /><br />SET NOCOUNT ON<br />DECLARE @ErrorMessage VARCHAR(500)<br />, @Remarksvarchar(1000)<br />, @ProcNamevarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @iDoc int<br />DECLARE @CustSalesLead TABLE (MaxNumResults int, ContentType varchar(20))<br />DECLARE @CustSalesLeadDealers TABLE (WaterfallType varchar(20), SortOrder tinyint)<br />DECLARE @InCustomerSalesLead xml<br />SET @InCustomerSalesLead = ‘<CustomerSalesLead><br /><CustomerSalesLeadID>256</CustomerSalesLeadID><br /><DeliveryStatusId>0</DeliveryStatusId><br /><DeliveryStatusDate>Mar 26 2007 15:45:13</DeliveryStatusDate><br /><DeliveryReceiptId>0758</DeliveryReceiptId><br /><WebsiteAffiliateSourceId>18</WebsiteAffiliateSourceId><br /><BrandId>1</BrandId><br /><SalesLeadDate>Mar 26 2007 15:45:13</SalesLeadDate><br /><VehicleStatus>Available</VehicleStatus><br /><VehicleInventoryId>XYZ 007</VehicleInventoryId><br /><vehicleYear>2001</vehicleYear><br /><VehicleMakeName>Something</VehicleMakeName><br /><VehicleModelName>SUV</VehicleModelName><br /><VehicleTrimName>Some trim</VehicleTrimName><br /><VehicleInteriorColor>Grey</VehicleInteriorColor><br /><VehicleExteriorColor>BLACK</VehicleExteriorColor><br /><VehiclePreferredFinanceMethod>Need Finance</VehiclePreferredFinanceMethod><br /><VehicleDownpayment>2000</VehicleDownpayment><br /><VehicleOptions><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ower Steering</VehicleOptions><br /><VehicleComments>Looking Good</VehicleComments><br /><TradeInYear>2006</TradeInYear><br /><TradeInMakeName>Something</TradeInMakeName><br /><TradeInModelName>Honda</TradeInModelName><br /><TradeInTrimName>SUV</TradeInTrimName><br /><TradeInOdometer>25000</TradeInOdometer><br /><CustomerFirstName>Stephen</CustomerFirstName><br /><CustomerLastName>John Son</CustomerLastName><br /><CustomerEmailAddress>[email protected]</CustomerEmailAddress><br /><CustomerStreetAddress1>3801 Parkview Ln</CustomerStreetAddress1><br /><CustomerStreetAddress2>Apt 29A</CustomerStreetAddress2><br /><CustomerCityName>Irvine</CustomerCityName><br /><CustomerStateAbbrev>CA</CustomerStateAbbrev><br /><CustomerPostalCode>92712</CustomerPostalCode><br /><CustomerHomePhone>992 889 9989</CustomerHomePhone><br /><CustomerWorkPhone>949 552 2225</CustomerWorkPhone><br /><CustomerFaxPhone>174 226 5558</CustomerFaxPhone><br /><CustomerBestContactMethod>Evening</CustomerBestContactMethod><br /><CustomerBestContactTime>7PM-8PM</CustomerBestContactTime><br /><CustomerPurchaseTimeFrame>6 months</CustomerPurchaseTimeFrame><br /><CustomerComments>I want to buy this car</CustomerComments><br /><CustomerSalesLeadDealer><br /><CustomerSalesLeadDealerId>256</CustomerSalesLeadDealerId><br /><DealerCityName>Santa Ana</DealerCityName><br /><DealerCode>128</DealerCode><br /><DealerName>Sagar</DealerName><br /><DealerPostalCode>97888</DealerPostalCode><br /><DealerStateAbbrev>CA</DealerStateAbbrev><br /><DealerStreetAddress>9227 ParkAve</DealerStreetAddress><br /><DealerTravelDistance>10 Miles</DealerTravelDistance><br /><DealerVoicePhone>225 552 5525</DealerVoicePhone><br /><DealerLatitude>10.26</DealerLatitude><br /><DealerLongitude>23.36</DealerLongitude><br /><CustomerSalesLeadId>236</CustomerSalesLeadId><br /><DealerId>11</DealerId><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rogramId>25</ProgramId><br /></CustomerSalesLeadDealer><br /><CustomerSalesLeadDealer><br /><CustomerSalesLeadDealerId>362</CustomerSalesLeadDealerId><br /><DealerCityName>Tustin</DealerCityName><br /><DealerCode>0125</DealerCode><br /><DealerName>Kalapala</DealerName><br /><DealerPostalCode>24244</DealerPostalCode><br /><DealerStateAbbrev>CA</DealerStateAbbrev><br /><DealerStreetAddress>29 Here only Ave</DealerStreetAddress><br /><DealerTravelDistance>5 miles</DealerTravelDistance><br /><DealerVoicePhone>234 555 5567</DealerVoicePhone><br /><DealerLatitude>21.25</DealerLatitude><br /><DealerLongitude>31.25</DealerLongitude><br /><CustomerSalesLeadDealerId>362</CustomerSalesLeadDealerId><br /><CustomerSalesLeadId>254</CustomerSalesLeadId><br /><DealerId>101</DealerId><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rogramId>2565</ProgramId><br /></CustomerSalesLeadDealer><br /><CommunicationsOptions><br /><option>AUTOMOTIVE_TRENDS</option><br /></CommunicationsOptions><CommunicationsOptions><option>VECHILE_REVIEWS</option><br /></CommunicationsOptions><br /></CustomerSalesLead>'<br /><br />EXEC sp_xml_preparedocument @iDoc OUTPUT, @InCustomerSalesLead<br /><br />SELECT *<br />FROM OPENXML(@iDoc, N’/CustomerSalesLead’,2)<br />WITH (DeliveryStatusId int,<br />DeliveryStatusDate VARCHAR(60),<br />DeliveryReceiptId varchar(32),<br />WebsiteAffiliateSourceId int,<br />BrandId int,<br />SalesLeadDate VARCHAR(60),<br />VehicleStatus varchar(4),<br />VehicleInventoryId varchar(32),<br />vehicleYear smallint,<br />VehicleMakeName varchar(50),<br />VehicleModelName varchar(50),<br />VehicleTrimName varchar(50),<br />VehicleInteriorColor varchar(50),<br />VehicleExteriorColor varchar(50),<br />VehiclePreferredFinanceMethod varchar(10),<br />V ehicleDownpayment int,<br />VehicleOptions varchar(1000),<br />VehicleComments varchar(1000),<br />TradeInYear smallint,<br />TradeInMakeName varchar(50),<br />TradeInModelName varchar(50),<br />TradeInTrimName varchar(50),<br />TradeInOdometer int,<br />CustomerFirstName varchar(30),<br />CustomerLastName varchar(30),<br />CustomerEmailAddress varchar(255),<br />CustomerStreetAddress1 varchar(100),<br />CustomerStreetAddress2 varchar(100),<br />CustomerCityName varchar(50),<br />CustomerStateAbbrev varchar(4),<br />CustomerPostalCode varchar(10),<br />CustomerHomePhone varchar(20),<br />CustomerWorkPhone varchar(20),<br />CustomerFaxPhone varchar(20),<br />CustomerBestContactMethod varchar(50),<br />CustomerBestContactTime varchar(50),<br />CustomerPurchaseTimeFrame varchar(50),<br />CustomerComments varchar(1000))<br /><br /><br />SELECT * <br />FROM OPENXML(@iDoc, N’/CustomerSalesLead/CustomerSalesLeadDealer’,2)<br />WITH(<br />DealerName varchar(255),<br />DealerStreetAddress varchar(100),<br />DealerCityName varchar(50),<br />DealerStateAbbrev varchar(4),<br />DealerPostalCode varchar(10),<br />DealerVoicePhone varchar(20),<br />DealerTravelDistance varchar(50),<br />DealerLatitude float,<br />DealerLongitude float,<br />DealerCode varchar(50),<br />DealerId int,<br />ProgramId int<br />)<br /><br />SELECT [option] <br />FROM OPENXML(@iDoc, N’/CustomerSalesLead/CommunicationsOptions’,2)<br />WITH(<br />[option] varchar(20)<br />)<br /><br />EXEC sp_xml_removedocument @iDoc <br /><br /><br /><br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS†with no warranties for accuracy.<br />DOn’t have exp. in XML seehttp://msdn2.microsoft.com/en-us/library/ms345115.aspx,http://blogs.msdn.com/mrorke/ etc. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I got the solution… thanks… Select T.c.value(‘DeliveryStatusId[1]’,’int’) As ‘DeliveryStatusId’,
T.c.value(‘DeliveryStatusDate[1]’,’varchar(60)’) As ‘DeliveryStatusDate’,
T.c.value(‘DeliveryReceiptId[1]’,’varchar(32)’) As ‘DeliveryReceiptId’,
T.c.value(‘WebsiteAffiliateSourceId[1]’,’int’) As ‘WebsiteAffiliateSourceId’,
T.c.value(‘BrandId[1]’,’int’) As ‘BrandId’,
T.c.value(‘SalesLeadDate[1]’,’varchar(60)’) As ‘SalesLeadDate’,
T.c.value(‘VehicleStatus[1]’,’varchar(4)’) As ‘VehicleStatus’,
T.c.value(‘VehicleInventoryId[1]’,’varchar(32)’) As ‘VehicleInventoryId’,
T.c.value(‘vehicleYear[1]’,’smallint’) As ‘vehicleYear’,
T.c.value(‘VehicleMakeName[1]’,’varchar(50)’) As ‘VehicleMakeName’,
T.c.value(‘VehicleModelName[1]’,’varchar(50)’) As ‘VehicleModelName’,
T.c.value(‘VehicleTrimName[1]’,’varchar(50)’) As ‘VehicleTrimName’,
T.c.value(‘VehicleInteriorColor[1]’,’varchar(50)’) As ‘VehicleInteriorColor’,
T.c.value(‘VehicleExteriorColor[1]’,’varchar(50)’) As ‘VehicleExteriorColor’,
T.c.value(‘VehiclePreferredFinanceMethod[1]’,’varchar(10)’) As ‘VehiclePreferredFinanceMethod’,
T.c.value(‘VehicleDownpayment[1]’,’int’) As ‘VehicleDownpayment’,
T.c.value(‘VehicleOptions[1]’,’varchar(1000)’) As ‘VehicleOptions’,
T.c.value(‘VehicleComments[1]’,’varchar(1000)’) As ‘VehicleComments’,
T.c.value(‘TradeInYear[1]’,’smallint’) As ‘TradeInYear’,
T.c.value(‘TradeInMakeName[1]’,’varchar(50)’) As ‘TradeInMakeName’,
T.c.value(‘TradeInModelName[1]’,’varchar(50)’) As ‘TradeInModelName’,
T.c.value(‘TradeInTrimName[1]’,’varchar(50)’) As ‘TradeInTrimName’,
T.c.value(‘TradeInOdometer[1]’,’int’) As ‘TradeInOdometer’,
T.c.value(‘CustomerFirstName[1]’,’varchar(30)’) As ‘CustomerFirstName’,
T.c.value(‘CustomerLastName[1]’,’varchar(630)’) As ‘CustomerLastName’,
T.c.value(‘CustomerEmailAddress[1]’,’varchar(255)’) As ‘CustomerEmailAddress’,
T.c.value(‘CustomerStreetAddress1[1]’,’varchar(100)’) As ‘CustomerStreetAddress1’,
T.c.value(‘CustomerStreetAddress2[1]’,’varchar(100)’) As ‘CustomerStreetAddress2’,
T.c.value(‘CustomerCityName[1]’,’varchar(50)’) As ‘CustomerCityName’,
T.c.value(‘CustomerStateAbbrev[1]’,’varchar(4)’) As ‘CustomerStateAbbrev’,
T.c.value(‘CustomerPostalCode[1]’,’varchar(10)’) As ‘CustomerPostalCode’,
T.c.value(‘CustomerHomePhone[1]’,’varchar(20)’) As ‘CustomerHomePhone’,
T.c.value(‘CustomerWorkPhone[1]’,’varchar(20)’) As ‘CustomerWorkPhone’,
T.c.value(‘CustomerFaxPhone[1]’,’varchar(20)’) As ‘CustomerFaxPhone’,
T.c.value(‘CustomerBestContactMethod[1]’,’varchar(50)’) As ‘CustomerBestContactMethod’,
T.c.value(‘CustomerBestContactTime[1]’,’varchar(50)’) As ‘CustomerBestContactTime’,
T.c.value(‘CustomerPurchaseTimeFrame[1]’,’varchar(50)’) As ‘CustomerPurchaseTimeFrame’,
T.c.value(‘CustomerComments[1]’,’varchar(1000)’) As ‘CustomerComments’
From @InCustomerSalesLead.nodes(‘/CustomerSalesLead’) T(c) Select T.c.value(‘DealerName[1]’,’varchar(255)’) As ‘DealerName’,
T.c.value(‘DealerStreetAddress[1]’,’varchar(100)’) As ‘DealerStreetAddress’,
T.c.value(‘DealerCityName[1]’,’varchar(50)’) As ‘DealerCityName’,
T.c.value(‘DealerStateAbbrev[1]’,’varchar(50)’) As ‘DealerStateAbbrev’,
T.c.value(‘DealerPostalCode[1]’,’varchar(10)’) As ‘DealerPostalCode’,
T.c.value(‘DealerVoicePhone[1]’,’varchar(20)’) As ‘DealerVoicePhone’,
T.c.value(‘DealerTravelDistance[1]’,’varchar(50)’) As ‘DealerTravelDistance’,
T.c.value(‘DealerLatitude[1]’,’float’) As ‘DealerLatitude’,
T.c.value(‘DealerLongitude[1]’,’float’) As ‘DealerLongitude’,
T.c.value(‘DealerCode[1]’,’varchar(50)’) As ‘DealerCode’,
T.c.value(‘DealerId[1]’,’int’) As ‘DealerId’,
T.c.value(‘ProgramId[1]’,’int’) As ‘ProgramId’
From @InCustomerSalesLead.nodes(‘/CustomerSalesLead/CustomerSalesLeadDealer’) T(c) Select T.c.value(‘option[1]’,’varchar(255)’) As ‘option’
From @InCustomerSalesLead.nodes(‘/CustomerSalesLead/CommunicationsOptions’) T(c)
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>