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.
I found using
sqlxml
is very handy for quering xml, in your case you neednodes()
andvalue()
functions:It's also possible to do with nested
nodes()
functions:But in any case, to get all reviews, you have to path
ns2:procedure1/auth/reviews/reviews
xpath toopenxml
(ornodes()
) function, so you'll have all reviews as rows.If you're really want to use
openxml
, then change your query like:sql fiddle demo