December 22, 2008 at 1:56 am
Hi All,
This is one problem which is driving me nuts for the last few days. I have a query which generates an XML using XML Path. This XML is converted to NVARCHAR(MAX) type and the output is passed to a file using SSIS. But, however, the data I'm getting is a truncated version of the original data. After a certain size, the nodes are mssing. Then I executed the SP standalone and found that while converting the XML to NVARCHAR(MAX), the truncation is occurring. Any suggestion would be helpful
December 22, 2008 at 2:10 am
Could you pls post your script to generate XML and conversion to NVARCHAR(MAX)?
Regards,
Nitin
December 22, 2008 at 2:14 am
How are you storing the xml that you generate? Are you using the xml data type on SQL Server 2005?
An example would certainly help.
- Regards,
Andras
December 22, 2008 at 3:15 am
SELECT
CAST(
(
SELECT
--Address Tag
'Public' AS 'Address/privacyType',
1 AS 'Address/preference-order',
1 AS 'Address/address-preference-order',
'Display' AS 'Address/category',
CASE WHEN (ISNULL(LAD.szAddress1_nm,'') + ' ' + ISNULL(LAD.szAddress2_nm,'')) = '' THEN NULL ELSE (ISNULL(LAD.szAddress1_nm,'') + ' ' + ISNULL(LAD.szAddress2_nm,'')) END AS 'Address/FullStreetAddress',
CASE WHEN ISNULL(LAD.szCity_nm,'')<>'' THEN LAD.szCity_nm ELSE NULL END AS 'Address/City',
CASE WHEN ISNULL(LAD.szStateProvince_cd,'')<>'' THEN LAD.szStateProvince_cd ELSE NULL END AS 'Address/StateOrProvince',
CASE WHEN ISNULL(LAD.szPostal_cd,'')<>'' THEN RTRIM(LAD.szPostal_cd) ELSE NULL END AS 'Address/PostalCode',
CASE WHEN ISNULL(LAD.sCountry_cd,'')<>'' THEN LAD.sCountry_cd ELSE NULL END AS 'Address/Country',
--ListPrice Tag
CASE WHEN ISNULL(L.mListPrice_amt,'')<>'' THEN L.mListPrice_amt ELSE NULL END AS 'ListPrice',
--ListingURL
CASE
WHEN @szBrand_cd = 'SIR' THEN 'www.sothebysrealty.com'
ELSE REPLACE(@szListingURL_tx, ' ', CONVERT(VARCHAR(36),L.gListing_GUID))
END AS 'ListingURL',
--ProviderName
CASE WHEN ISNULL(CB.szBrandDesc_tx,'')<>'' THEN CB.szBrandDesc_tx ELSE NULL END AS 'ProviderName',
--ProviderURL
CASE WHEN ISNULL(CB.szURL_tx,'')<>'' THEN CB.szURL_tx ELSE NULL END AS 'ProviderURL',
--Bedrooms
CASE WHEN ISNULL(L.iBedRoom_no,'')<>'' THEN L.iBedRoom_no ELSE NULL END AS 'Bedrooms',
--BathRooms
CASE WHEN ISNULL(L.iBath_no,'')<>'' THEN L.iBath_no ELSE NULL END AS 'Bathrooms',
--PropertyType
CASE
WHEN L.byPropertyGroup_sk = 2 --Resale -Condominium/Townhouse
OR L.byPropertyGroup_sk = 4 --New -Condominium/Townhouse
THEN 'Condo'
WHEN L.byPropertyGroup_sk = 5 --Mobile/Manufactured Home
OR L.byPropertyGroup_sk = 6 --Mobile/Manufactured Hm w Land
THEN 'Mobile/Manufactured'
WHEN L.byPropertyGroup_sk = 7 --Multi-Family (2-4 Units) RESID
THEN 'Multi Family'
WHEN L.byPropertyGroup_sk = 8 --Farm/Ranch
THEN 'Farm/Ranch'
WHEN L.byPropertyGroup_sk = 9 --Residential Lot
OR L.byPropertyGroup_sk = 10 --Vacant Land (0-10 Acres) RESID
OR L.byPropertyGroup_sk = 11 --Vacant Land (10+ Acres)
THEN 'Lot/Land'
WHEN L.byPropertyGroup_sk = 13 --Co-Op
THEN 'Coop'
WHEN L.byPropertyGroup_sk = 14 --Other Residential
THEN 'Other'
ELSE 'Single Family'
END AS 'PropertyType',
--ListingKey
L.gListing_GUID AS 'ListingKey',
--ListingCategory
'Purchase' AS 'ListingCategory',
--ListingStatus
CASE
WHEN L.szListingStatus_cd = 'AC' THEN 'Active'
WHEN L.szListingStatus_cd = 'OP' THEN 'Pending'
END AS 'ListingStatus',
--Photos
(
SELECT
CASE WHEN ISNULL(M.szMediaURL_tx,'')<>'' THEN M.szMediaURL_tx ELSE NULL END AS 'Photo/URL',
CASE WHEN ISNULL(M.szCaption_tx,'')<>'' THEN M.szCaption_tx ELSE NULL END AS 'Photo/Caption',
CASE WHEN ISNULL(M.szShortDesc_tx,'')<>'' THEN M.szShortDesc_tx ELSE NULL END AS 'Photo/Description',
CASE WHEN ISNULL(M.byOrder_no,'')<>'' THEN M.byOrder_no ELSE NULL END AS 'Photo/SequenceNumber'
FROM dbo.tblListingMedia M (NOLOCK)
WHERE M.lListing_sk = L.lListing_sk
AND M.sMediaType_cd = 'JPG'
FOR XML PATH (''),Type
) AS 'Photos',
--DiscloseAddress
CASE
WHEN L.bShowAddrOnInet_fl = 1 THEN 'true'
ELSE 'false'
END AS 'DiscloseAddress',
--'false' AS 'DiscloseAddress',
--ListingDescription
CASE WHEN ISNULL(dbo.udf_LDListingRemarkGet(L.lListing_sk),'')<>'' THEN dbo.udf_LDListingRemarkGet(L.lListing_sk) ELSE NULL END AS 'ListingDescription' ,
--MlsNumber
CASE WHEN ISNULL(LMLS.szMLS_no,'')<>'' THEN LMLS.szMLS_no ELSE NULL END AS 'MlsNumber',
--YearBuilt
CASE WHEN dbo.udf_CheckIntegers(L.iYearBuilt_no, @iMaxYearBuilt) = 0 THEN NULL
ELSE dbo.udf_CheckIntegers(L.iYearBuilt_no, @iMaxYearBuilt)
END AS 'YearBuilt',
--ListingDate
CASE WHEN ISNULL(L.dtListingActive_dt,'')<>'' THEN CONVERT(VARCHAR(10),L.dtListingActive_dt,120) ELSE NULL END AS 'ListingDate',
--ListingTitle
CASE WHEN @szBrand_cd = 'BHG'
OR @szBrand_cd = 'C21'
THEN CASE WHEN ISNULL(LAD.szCity_nm,'') = '' THEN '' ELSE LAD.szCity_nm + ', ' END
+ CASE WHEN ISNULL(LAD.szStateProvince_cd,'') = '' THEN '' ELSE LAD.szStateProvince_cd + ' - ' END + CPT.szPropertyType_nm
WHEN @szBrand_cd = 'CB'
THEN 'property for sale in ' + ISNULL(LAD.szCity_nm,'') + ', ' + ISNULL(LAD.szStateProvince_cd,'')
WHEN @szBrand_cd = 'ERA'
THEN ISNULL(LAD.szCity_nm,'') + ', ' + ISNULL(LAD.szStateProvince_cd,'') + ' ' + 'Home For Sale'
END AS 'ListingTitle',
--FullBathrooms
CASE WHEN ISNULL(L.iFullBath_no,'')<>'' THEN L.iFullBath_no ELSE NULL END AS 'FullBathrooms',
--PartialBathrooms
CASE WHEN ISNULL(L.iHalfBath_no,'')<>'' THEN L.iHalfBath_no ELSE NULL END AS 'PartialBathrooms',
--ListingParticipant
(
SELECT
CASE WHEN ISNULL(P.szFirst_nm,'')<>'' THEN P.szFirst_nm ELSE NULL END AS 'Participant/FirstName',
CASE WHEN ISNULL(P.szLast_nm,'')<>'' THEN P.szLast_nm ELSE NULL END AS 'Participant/LastName',
'Listing' AS 'Participant/Role',
ISNULL
(
[dbo].[udf_LDYahooParticipantPhoneGet](L.lListing_sk, 2),OP.szPhone_no --2 For Business Phone
) AS 'Participant/OfficePhone',
[dbo].[udf_LDYahooParticipantPhoneGet](L.lListing_sk,8) AS 'Participant/MobilePhone', -- For Mobile Phone
CASE WHEN ISNULL(P.szEmail_tx,'')<>'' THEN P.szEmail_tx ELSE NULL END AS 'Participant/Email',
[dbo].[udf_LDYahooParticipantPhoneGet](L.lListing_sk, 5) AS 'Participant/Fax', -- For Fax
CASE WHEN ISNULL(P.szURL_tx,'')<>'' THEN P.szURL_tx ELSE NULL END AS 'Participant/WebsiteURL',
CASE WHEN ISNULL(P.szMediaURL_tx,'')<>'' THEN P.szMediaURL_tx ELSE NULL END AS 'Participant/PhotoURL',
CASE WHEN ISNULL(CONVERT(VARCHAR(36),P.gParticipant_GUID),'')<>'' THEN P.gParticipant_GUID ELSE NULL END AS 'Participant/ParticipantId'
FROM dbo.tblParticipant P WITH (NOLOCK)
INNER JOIN dbo.tblListingParticipant LP WITH (NOLOCK)
ON P.lParticipant_sk = LP.lParticipant_sk
WHERE LP.lListing_sk = L.lListing_sk
FOR XML PATH (''),Type
) AS 'ListingParticipants',
--VirtualTours
(
SELECT
CASE WHEN ISNULL(M.szMediaURL_tx,'')<>'' THEN M.szMediaURL_tx ELSE NULL END AS 'VirtualTour/URL',
CASE WHEN ISNULL(M.szCaption_tx,'')<>'' THEN M.szCaption_tx ELSE NULL END AS 'VirtualTour/Caption',
CASE WHEN ISNULL(M.szShortDesc_tx,'')<>'' THEN M.szShortDesc_tx ELSE NULL END AS 'VirtualTour/Description',
CASE WHEN ISNULL(CONVERT(VARCHAR(2),M.byOrder_no),'')<>'' THEN M.byOrder_no ELSE NULL END AS 'VirtualTour/SequenceNumber'
FROM dbo.tblListingMedia M (NOLOCK)
WHERE M.lListing_sk = L.lListing_sk
AND M.sMediaType_cd = 'BBO'
FOR XML PATH (''),Type
) AS 'VirtualTours',
--Videos
(
SELECT
CASE WHEN ISNULL(M.szMediaURL_tx,'')<>'' THEN M.szMediaURL_tx ELSE NULL END AS 'Video/URL',
CASE WHEN ISNULL(M.szCaption_tx,'')<>'' THEN M.szCaption_tx ELSE NULL END AS 'Video/Caption',
CASE WHEN ISNULL(M.szShortDesc_tx,'')<>'' THEN M.szShortDesc_tx ELSE NULL END AS 'Video/Description',
CASE WHEN ISNULL(M.byOrder_no,'')<>'' THEN M.byOrder_no ELSE NULL END AS 'Video/SequenceNumber'
FROM dbo.tblListingMedia M (NOLOCK)
WHERE M.lListing_sk = L.lListing_sk
AND M.sMediaType_cd = 'VWT'
FOR XML PATH (''),Type
) AS 'Videos',
--Brokerage
CASE
WHEN SUBSTRING(O.szoffice_nm, 1,LEN(@lBrand_desc)) = @lBrand_desc
THEN LTRIM(SUBSTRING(O.szoffice_nm,(LEN(@lBrand_desc)+1),LEN(O.szoffice_nm)))
WHEN SUBSTRING(O.szoffice_nm,(LEN(RTRIM(O.szoffice_nm)) - LEN(@lBrand_desc)), LEN(RTRIM(O.szoffice_nm))) = @lBrand_desc
THEN SUBSTRING(O.szoffice_nm, 1, (LEN(RTRIM(O.szoffice_nm)) - LEN(@lBrand_desc)))
ELSE O.szoffice_nm
END AS 'Brokerage/Name' ,
CASE WHEN ISNULL(CONVERT(VARCHAR(20),OP.szPhone_no),'')<>'' THEN OP.szPhone_no ELSE NULL END AS 'Brokerage/Phone',
CASE WHEN ISNULL(O.szEmail_tx,'')<>'' THEN O.szEmail_tx ELSE NULL END AS 'Brokerage/Email',
CASE WHEN ISNULL(O.szURL_tx,'')<>'' THEN O.szURL_tx ELSE NULL END AS 'Brokerage/WebsiteURL',
-- CASE WHEN O.bShowAddrOnInet_fl = 1 THEN 'Public'
-- END
'Public' AS 'Brokerage/Address/privacyType',
1 AS 'Brokerage/Address/preference-order',
1 AS 'Brokerage/Address/address-preference-order',
'Display' AS 'Brokerage/Address/category',
CASE WHEN (ISNULL(O.szAddress1_nm,'') + ' ' + ISNULL(O.szAddress2_nm,'')) = '' THEN NULL ELSE (ISNULL(O.szAddress1_nm,'') + ' ' + ISNULL(O.szAddress2_nm,'')) END AS 'Brokerage/Address/FullStreetAddress',
CASE WHEN ISNULL(O.szCity_nm,'')<>'' THEN O.szCity_nm ELSE NULL END AS 'Brokerage/Address/City',
CASE WHEN ISNULL(O.szStateProvince_cd,'')<>'' THEN O.szStateProvince_cd ELSE NULL END AS 'Brokerage/Address/StateOrProvince',
CASE WHEN ISNULL(O.szPostal_cd,'')<>'' THEN O.szPostal_cd ELSE NULL END AS 'Brokerage/Address/PostalCode',
CASE WHEN ISNULL(O.sCountry_cd,'')<>'' THEN O.sCountry_cd ELSE NULL END AS 'Brokerage/Address/Country' ,
--Location
CASE WHEN ISNULL(CONVERT(VARCHAR(60),L.dLatitude_no),'')<>'' THEN L.dLatitude_no ELSE NULL END AS 'Location/Latitude',
CASE WHEN ISNULL(CONVERT(VARCHAR(60),L.dLongitude_no),'')<>'' THEN L.dLongitude_no ELSE NULL END AS 'Location/Longitude',
--Neighborhoods
CASE WHEN ISNULL(LD.szNeighborhood_nm,'')<>'' THEN LD.szNeighborhood_nm ELSE NULL END AS 'Location/Neighborhoods/Neighborhood/Name',
--OpenHouses
(
SELECT
CASE WHEN ISNULL(LOH.dtStart_dt,'')<>'' THEN CONVERT(VARCHAR(10),LOH.dtStart_dt,120) ELSE NULL END AS 'OpenHouse/Date',
CASE WHEN ISNULL(LOH.dtStart_dt,'')<>'' THEN LTRIM(RIGHT(CONVERT(varchar,LOH.dtStart_dt,100),8)) ELSE NULL END AS 'OpenHouse/StartTime',
CASE WHEN ISNULL(LOH.dtEnd_dt,'')<>'' THEN LTRIM(RIGHT(CONVERT(varchar,LOH.dtEnd_dt,100),8)) ELSE NULL END AS 'OpenHouse/EndTime',
CASE WHEN ISNULL(LOH.szRemark_tx,'')<>'' THEN LOH.szRemark_tx ELSE NULL END AS 'OpenHouse/Description'
FROM dbo.tblListingOpenHouse LOH WITH (NOLOCK)
WHERE LOH.lListing_sk = L.lListing_sk
--AND LOH.dtStart_dt >= GetDate()
ORDER BY LOH.dtStart_dt ASC
FOR XML PATH (''),Type
) AS 'OpenHouses',
'' AS 'Schools/SchoolDistrict',
CASE WHEN ISNULL(LD.mAnnualTaxes_amt,'')<>'' THEN LD.mAnnualTaxes_amt ELSE NULL END AS 'Taxes/Tax/Amount',
--DetailedCharacteristics
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'ArchitectureStyle') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Basement',4,'114' )AS 'DetailedCharacteristics/Basement',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'CableReady',3,'450') AS 'DetailedCharacteristics/CableReady',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'CoolingSystem') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Deck', 3,'37,38,79,458') AS 'DetailedCharacteristics/Deck',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk, 'Dock',3,'28,39,439') AS 'DetailedCharacteristics/Dock',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Doorman', 3,'40') AS 'DetailedCharacteristics/Doorman',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Elevator', 3,'42,84') AS 'DetailedCharacteristics/Elevator',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'ExteriorType') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Fireplace', 10,'154') AS 'DetailedCharacteristics/Fireplace',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'FloorCovering') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'GatedEntry', 3,'50,56') AS 'DetailedCharacteristics/GatedEntry',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'HeatingFuel') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'HeatingSystem') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'HottubSpa', 3,'63,95') AS 'DetailedCharacteristics/HottubSpa',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Intercom', 3,'71') AS 'DetailedCharacteristics/Intercom',
CASE
WHEN L.bNewConstruction_fl = 1 THEN 'true'
ELSE 'false'
END AS 'DetailedCharacteristics/NewConstruction',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'ParkingType') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Patio', 3,'22,64,66,68,430,449') AS 'DetailedCharacteristics/Patio',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk, 'Pond',3,'80') AS 'DetailedCharacteristics/Pond',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Pool', 3,'22,64,66,68,430,449') AS 'DetailedCharacteristics/Pool',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Porch', 3,'81,442') AS 'DetailedCharacteristics/Porch',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'RoofType') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'RVParking', 3,'88') AS 'DetailedCharacteristics/RVParking',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk, 'Sauna',3,'89') AS 'DetailedCharacteristics/Sauna',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'SecuritySystem', 3,'90') AS 'DetailedCharacteristics/SecuritySystem',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'SprinklerSystem', 3,'96') AS 'DetailedCharacteristics/SprinklerSystem',
[dbo].[udf_LDYahooFeatureMap] (L.lListing_sk, 'ViewType') AS 'DetailedCharacteristics',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Waterfront', 24,'318,320') AS 'DetailedCharacteristics/Waterfront',
[dbo].[udf_LDYahooFeatureGet] (L.lListing_sk,'Wired', 3,'450,451,452,453') AS 'DetailedCharacteristics/Wired'
FROM dbo.tblListing L WITH (NOLOCK)
INNER JOIN dbo.tblListingAddress LAD WITH (NOLOCK) ON L.lListing_sk = LAD.lListing_sk
INNER JOIN dbo.tblListingDetail LD WITH (NOLOCK) ON L.lListing_sk = LD.lListing_sk
LEFT JOIN dbo.tblListingMLS LMLS WITH (NOLOCK) ON LMLS.lListing_sk = L.lListing_sk AND LMLS.bySeq_no = 1
INNER JOIN dbo.tblListingOffice LO WITH (NOLOCK) ON LO.lListing_sk = L.lListing_sk
INNER JOIN dbo.tblOffice O WITH (NOLOCK) ON O.lOffice_sk = LO.lOffice_sk
LEFT JOIN dbo.tblOfficePhone OP WITH (NOLOCK) ON LO.lOffice_sk = OP.lOffice_sk AND OP.ByPhoneType_sk = 2
INNER JOIN dbo.tblCodeBrand CB WITH (NOLOCK) ON CB.byBrand_sk = L.byBrand_sk
INNER JOIN dbo.tblCodePropertyType CPT WITH (NOLOCK) ON L.iPropertyType_sk = CPT.iPropertyType_sk
WHERE L.byBrand_sk =@lByBrand_sk
FOR XML PATH('Listing') )AS VARCHAR(MAX)) AS OutputXML
December 22, 2008 at 3:36 am
It looks, there is nothing wrong with your script. Have tried to store your output XML in file using management studio? Run your script from management studio and save your ourput in file, then check the file.
Best Regards
Nitin
Regards,
Nitin
December 23, 2008 at 2:55 am
Hey I got the sollution of your problem.
Converting the whole string to VARCHAR(MAX) doesn't solve the problem, if your string is longer than 8000 characters. You have to convert each string inside your SELECT statement to VARCHAR(max).
Best Regards
Nitin
Regards,
Nitin
April 28, 2009 at 9:02 am
Hi,
I ran into the same problem today converting an xml set from an sproc to varchar(max). I my case I execute a product query from a web portal which invokes a wcf endpoint that queries the SQL Server 2005 database. All data layer access is done through sprocs. The sproc generates xml and converts the xml datatype to a varchar(max) that it returns.
At first my results where truncated to 64KB. I executed the sproc from sqlserver mgmt. studio and the output was truncated in the output grid:
// ... generate xml
// return varchar(max) From sproc
select convert(varchar(max), @xmlresult) => truncated to 64KB in sms. BUT:
select len(convert(varchar(max), @xmlresult) ) is ok. (> 64KB)
So, it is a SQL client config issue. I solved it in the business tier by replacing VARCHAR(MAX) to VARCHAR(-1) in the dbml implementation file. That did the job. Check http://msdn.microsoft.com/en-us/library/a1904w6t(VS.80).aspx for more info on the topic. It's from a programmer's perspective but it might give you some more insights on the issue you have.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply