Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Namespace query common elements Expand / Collapse
Author
Message
Posted Saturday, March 27, 2010 6:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 24, 2014 10:11 AM
Points: 6, Visits: 116
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!
Post #891375
Posted Saturday, March 27, 2010 6:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:13 AM
Points: 20,575, Visits: 9,618
Look up coalesce and isnull in the books online (bol for short or sql server's help documentation).
Post #891377
Posted Saturday, March 27, 2010 8:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 24, 2014 10:11 AM
Points: 6, Visits: 116
Yup, that did it.
Did some reading here
In this case, I prefer coalesce as my plan is to expand the usefulness of this table to include multiple document types.

Thank you for your quick response !
Post #891382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse