March 27, 2010 at 6:21 pm
Greetings all, this is my first post.
I've searched through the archives and haven't found quite what I'm looking for, so here goes.
I have two XML schemas which I've created and added into the same xml schema collection.
The two schemas are very similar apart from a few items, and share most of the other elements.
For external validation reasons, there must be two different schemas, and thus I have created two different namespaces rather than importing the first schema into the second.
Both schemas have an element named "DOCTYPE" which I'd like to query into a single column for display purposes, as these two schemas represent two different xml documents.
To simplify the problem, I have ns1 and ns2 in schema collection sc1.
The table created has 3 columns, msgId -varchar, InsertDate, and xmldoc - associated with sc1.
Both documents are inserted into the table and are correctly validated.
Here it what I have so far:
WITH XMLNAMESPACES (
'http://a.b.com/schemas/type1' as ns1,
'http://a.b.com/schemas/type2' as ns2,
DEFAULT 'http://a.b.com/schemas/type1'
)
SELECT
msgId,
xmlDoc.value('(//DOCTYPE)[1]', 'nvarchar(max)') as DocType1,
xmlDoc.value('(//ns2:DOCTYPE)[1]', 'nvarchar(max)') as DocType2,
FROM xmlDocuments
The output looks something like this:
msgId DocType1 DocType2
abc A {null}
cba {null} B
How can I rewrite this query to combine the output into a single column with output like this:
msgId DocType
abc A
cba B
Any advise would be greatly appreciated.
Thank you in advance!
March 27, 2010 at 6:36 pm
Look up coalesce and isnull in the books online (bol for short or sql server's help documentation).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply