Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jasscat
Starting Member
9 Posts |
 Posted - 2008-07-21 : 14:33:45
|
| I want to create a new table with two columns: a key and an xml data type. The xml data type column will store various xml documents, so I can't tie it to one specific schema from the xml schema collections. At the same time, I want to be able to validate data I'm loading into the column with a schema specific to the data I'm loading. In the example below, I want to declare a local variable as xml and associate the local variable with a schema from the schema collection. Then I want to return values from a table and assign those values to the local xml variable. Finally, I want to load the new table with the key and xml data.So how do I associate the returned xml data with a namespace? I'm trying to use the WITH XMLNAMESPACE construct, but I'm getting an "incorrect syntax near the keyword 'WITH'" error. If I run this alone (from the WITH keyword down to the TYPE keyword), it works fine. If I assign the local variable with just the "select ..." statement, that also works (but without specifying the namespace). Declare @factor xml (dbo.deductible_factors)set @factor= WITH XMLNAMESPACES(DEFAULT 'http://schemas.xxxx.com/sqlserver/xxx/deductible.xsd')Select effective_date as 'Deductible_Factor/Effective_Date',deductible_amount as 'Deductible_Factor/Deductible_Amount',our_factor as 'Deductible_Factor/our_Factor'FROM dbo.my_deductible_factorsFOR XML PATH(''),root('Deductible_Factors'),TYPE)insert into factors (factor_name, factors) values ('Deductible', @factor) |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-07-22 : 05:16:03
|
Change the order and use select rather than set. Something like...Declare @factor xml (dbo.deductible_factors); WITH XMLNAMESPACES(DEFAULT 'http://schemas.xxxx.com/sqlserver/xxx/deductible.xsd')select @factor = (Select effective_date as 'Deductible_Factor/Effective_Date',deductible_amount as 'Deductible_Factor/Deductible_Amount',our_factor as 'Deductible_Factor/our_Factor'FROM dbo.my_deductible_factorsFOR XML PATH(''),root('Deductible_Factors'),TYPE))insert into factors (factor_name, factors) values ('Deductible', @factor)Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
jasscat
Starting Member
9 Posts |
Posted - 2008-07-22 : 08:22:24
|
| I tried something like that but couldn't get it to work. First, I thought someone would come along and slap my wrist for using "select" rather then "set." Second, I didn't use the semi-colon before the "With Namespaces." I tried it your way and while I didn't get it to run successfully yet because of some validation issues, I got it to recognize the namespace. Thanks! That semi-colon thing is really very poorly documented. I found one reference to it in some Microsoft documentation but can't seem to find that documentation again. Do you know where it is explained? |
|
|
|
|
|
|
|
|
Setting local xml variable with "With Namespaces"