How to create a flexible query for XML stored in SQL table

Question

I have the following XML file that is copied in my database

<ns2:procedure1 xmlns:ns2="http://www.endpoint.com/">
  <auth>
    <company>TEST Company</company>
    <lineOfBusiness>Sales</lineOfBusiness>
    <caseNumber>00001</caseNumber>
    <creationDate>2013-12-04</creationDate>
    <reviews>
      <reviews>
        <reviewNumber>ZA1010</reviewNumber>
        <revision>1</revision>
        <sequence>1</sequence>
     </reviews>
      <reviews>
        <reviewNumber>ZA1010</reviewNumber>
        <revision>2</revision>
        <sequence>2</sequence>
      </reviews>
      <reviews>
       <reviewNumber>ZA1010</reviewNumber>
        <revision>3</revision>
        <sequence>3</sequence>
      </reviews>
    </reviews>
  </auth>
</ns2:procedure1 xmlns:ns2="http://www.endpoint.com/">

I am using the following code:

DECLARE @XML AS XML
DECLARE @hDoc AS INT
DECLARE @SQL NVARCHAR (MAX)

SELECT  @XML = XMLData 
FROM    MYDatabase

DECLARE @rootxmlns VARCHAR(200)
SET     @rootxmlns = '<root xmlns:ns2="http://www.endpoint.com/"/>'

EXEC    sp_xml_preparedocument @hDoc OUTPUT, @XML, @rootxmlns

SELECT  *
FROM    OPENXML(@hDoc, N'/ns2:procedure1/auth')
WITH    (company NVARCHAR(25) 'company',
         lineOfBusiness NVARCHAR(50) 'lineOfBusiness',
         caseNumber NVARCHAR(30) 'caseNumber',
         creationDate DATETIME2 'creationDate',
         reviews_reviews_reviewNumber NVARCHAR(20)'reviews/reviews/reviewNumber',
         reviews_reviews_revision INT 'reviews/reviews/revision',
         reviews_reviews_sequence INT 'reviews/reviews/sequence')
EXEC    sp_xml_removedocument @hDoc

This query only retrieve the first review information but I need retrieve all the reviews information. I don't know what how many reviews has each block. Maybe one block has only one review but other block can has 10 reviews. I don't have any idea how to create a flexible query to do that. I appreciate any help.

Answer 1

I found using sqlxml is very handy for quering xml, in your case you need nodes() and value() functions:

;with xmlnamespaces ('http://www.endpoint.com/' as ns2)
select
    T.C.value('(../../company/text())[1]', 'nvarchar(max)') as Company,
    T.C.value('(../../lineOfBusiness/text())[1]', 'nvarchar(max)') as lineOfBusiness,
    T.C.value('(../../caseNumber/text())[1]', 'nvarchar(max)') as caseNumber,
    T.C.value('(../../creationDate/text())[1]', 'datetime2') as creationDate,
    T.C.value('(reviewNumber/text())[1]', 'nvarchar(max)') as reviewNumber,
    T.C.value('(revision/text())[1]', 'nvarchar(max)') as revision,
    T.C.value('(sequence/text())[1]', 'nvarchar(max)') as sequence
from @Data.nodes('ns2:procedure1/auth/reviews/reviews') as T(C)

It's also possible to do with nested nodes() functions:

;with xmlnamespaces ('http://www.endpoint.com/' as ns2)
select
    A.C.value('(company/text())[1]', 'nvarchar(max)') as Company,
    A.C.value('(lineOfBusiness/text())[1]', 'nvarchar(max)') as lineOfBusiness,
    A.C.value('(caseNumber/text())[1]', 'nvarchar(max)') as caseNumber,
    A.C.value('(creationDate/text())[1]', 'datetime2') as creationDate,
    R.C.value('(reviewNumber/text())[1]', 'nvarchar(max)') as reviewNumber,
    R.C.value('(revision/text())[1]', 'nvarchar(max)') as revision,
    R.C.value('(sequence/text())[1]', 'nvarchar(max)') as sequence
from @Data.nodes('ns2:procedure1/auth') as A(C)
    outer apply A.C.nodes('reviews/reviews') as R(C)

But in any case, to get all reviews, you have to path ns2:procedure1/auth/reviews/reviews xpath to openxml (or nodes()) function, so you'll have all reviews as rows.

If you're really want to use openxml, then change your query like:

...
SELECT  *
FROM    OPENXML(@hDoc, N'/ns2:procedure1/auth/reviews/reviews')
WITH    (company NVARCHAR(25) '../../company',
         lineOfBusiness NVARCHAR(50) '../../lineOfBusiness',
         caseNumber NVARCHAR(30) '../../caseNumber',
         creationDate DATETIME2 '../../creationDate',
         reviews_reviews_reviewNumber NVARCHAR(20)'reviewNumber',
         reviews_reviews_revision INT 'revision',
         reviews_reviews_sequence INT 'sequence')
...

sql fiddle demo