Multiple Namespace query common elements

• LarrySql

  SSC Journeyman

  Points: 96

  More actions

  March 27, 2010 at 6:21 pm

  #74599

  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!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  March 27, 2010 at 6:36 pm

  #1141010

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

• LarrySql

  SSC Journeyman

  Points: 96

  More actions

  March 27, 2010 at 8:08 pm

  #1141015

  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 !