July 12, 2007 at 3:47 am
I get an error when I include 'WITH XMLNAMESPACE' (Incorrect syntax near the keyword 'SET'.)
the statement works fine until I include the 'WITH XMLNAMESPACE' inside the SET statement.
ALTER
PROCEDURE [dbo].[uwmSP_GetScenarioPreStage]
AS
DECLARE
@xml XML
SET @xml = (
WITH XMLNAMESPACES (
DEFAULT 'http://xsdschema.uwm.com/mismoaus231'
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,'http://xsdschema.uwm.com/mismoaus231 ' as "schemaLocation"
)
Select
top(1)
isnull([ConcurrentLien],'N)'
) as "ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@ConcurrentOriginationLenderIndicator"
,'30' as "ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@RateLockPeriodDays"
,'BestEfforts' as ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@RateLockType
From dbo.Scenario
For
XML PATH('LOAN_APPLICATION')
)
SELECT
@xml
Frederick Goodrum
July 12, 2007 at 12:28 pm
did you mean to add an 'S' to the end of "XMLNAMESPACE"?
July 12, 2007 at 12:59 pm
I cut/paste the example from my working template. The template is the commands before I copy them into a 'Create Procedure' proc.
Yes, NAMESPACES works with the multiple values. If I remove the 'S', I get an error.
Frederick Goodrum
July 16, 2007 at 6:55 am
We need to use SELECT and put the "WITH namesapce" in the outside of the assignement, and use TYPE directive in the SELECT for XML statement The following should work:
ALTER PROCEDURE [dbo].[uwmSP_GetScenarioPreStage]
AS
DECLARE
@xml XML
WITH XMLNAMESPACES (
DEFAULT 'http://xsdschema.uwm.com/mismoaus231'
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,'http://xsdschema.uwm.com/mismoaus231 ' as "schemaLocation"
)
SELECTT @xml = (
Select
top(1)
isnull([ConcurrentLien],'N)'
) as "ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@ConcurrentOriginationLenderIndicator"
,'30' as "ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@RateLockPeriodDays"
,'BestEfforts' as ADDITIONAL_CASE_DATA/TRANSMITTAL_DATA/@RateLockType
From dbo.Scenario
For
XML PATH('LOAN_APPLICATION'), TYPE
)
SELECT
@xml
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply