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)
) 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)
) 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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy