Multiple Namespace query common elements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Multiple Namespace query common elements

Multiple Namespace query common elements

Rate Topic

Display Mode

Topic Options

Author

Message

LarrySql

Posted Saturday, March 27, 2010 6:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 12:42 PM
Points: 6, Visits: 110

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

Ninja's_RGR'us

Posted Saturday, March 27, 2010 6:36 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359, Visits: 9,541

Look up coalesce and isnull in the books online (bol for short or sql server's help documentation).

Post #891377

LarrySql

Posted Saturday, March 27, 2010 8:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 12:42 PM
Points: 6, Visits: 110

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 »

Permissions