CODEWITHSUNDEEP
t-sqlsoapsql-server-2012xquery
Joe
Joe

Reputation: 1055

Fully Import SOAP XML into SQL Server 2012

I have a SOAP response and need to import into SQL Server almost all fields of the soapenv:Body googleing and testing I build this query that seems working but it is not exactly what I was looking for:

declare 
@Root varchar(50)='/soap:Envelope/soap:Body/GetFeedbackResponse/', 
@xDoc XML = '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <ebl:RequesterCredentials soapenv:mustUnderstand="0" xmlns:ns="urn:ebay:apis:eBLBaseComponents" xmlns:ebl="urn:ebay:apis:eBLBaseComponents">
            <ebl:NotificationSignature xmlns:ebl="urn:ebay:apis:eBLBaseComponents">r6revSiTXCP9SBBFUtpDAQ==</ebl:NotificationSignature>
        </ebl:RequesterCredentials>
    </soapenv:Header>
    <soapenv:Body>
        <GetFeedbackResponse xmlns="urn:ebay:apis:eBLBaseComponents">
            <Timestamp>2015-09-06T11:20:48.528Z</Timestamp>
            <Ack>Success</Ack>
            <CorrelationID>428163922470</CorrelationID>
            <Version>899</Version>
            <Build>E899_INTL_APIFEEDBACK_17278558_R1</Build>
            <NotificationEventName>Feedback</NotificationEventName>
            <RecipientUserID>ebay_bestseller</RecipientUserID>
            <EIASToken>nY+sHZ2PrBmdj6wVnY+sEWDETj2dj6AFlIajDpaEpAydj6x9nY+seQ==</EIASToken>
            <FeedbackDetailArray>
                <FeedbackDetail>
                    <CommentingUser>ebay_bestseller</CommentingUser>
                    <CommentingUserScore>42425</CommentingUserScore>
                    <CommentText>Great buyer - We Would Appreciate 5 STARS for Our Feedback!</CommentText>
                    <CommentTime>2015-09-06T11:20:45.000Z</CommentTime>
                    <CommentType>Positive</CommentType>
                    <ItemID>310541589307</ItemID>
                    <Role>Buyer</Role>
                    <FeedbackID>1064451206013</FeedbackID>
                    <TransactionID>549674542021</TransactionID>
                    <OrderLineItemID>310541589307-549674542021</OrderLineItemID>
                </FeedbackDetail>
            </FeedbackDetailArray>
            <FeedbackDetailItemTotal>1</FeedbackDetailItemTotal>
            <FeedbackScore>126</FeedbackScore>
            <PaginationResult>
                <TotalNumberOfPages>1</TotalNumberOfPages>
                <TotalNumberOfEntries>1</TotalNumberOfEntries>
            </PaginationResult>
            <EntriesPerPage>25</EntriesPerPage>
            <PageNumber>1</PageNumber>
        </GetFeedbackResponse>
    </soapenv:Body>
</soapenv:Envelope>'

;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap], default 'urn:ebay:apis:eBLBaseComponents')
insert into Test (TS,Comment) 
select 
@xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/Timestamp)[1]', 'nvarchar(max)'),
@xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/FeedbackDetailArray/FeedbackDetail/CommentText)[1]', 'nvarchar(max)'),
........

First question: since have some 50 different SOAPs response, and some do have up to 120 Fiels, I was looking for a solution who flatten all fields and import automatically in the relative table withoud requiring to indicate all rows: considering I'm using sqlServer 2012/2014 is there a solution, or if not, is there a better/quicker way than what I proposed?

Second: (if there's no better solution) since I have a lot of fields to import, up to 120 for some SOAPs, and since root is always the same I would like to reduce the length putting in a variable the first part of the path so to have for example

@xDoc.value('('+@Root+'Timestamp)[1]', 'nvarchar(max)')

instead of 

@xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/Timestamp)[1]', 'nvarchar(max)')

but I receive an error like first value has to be literal value: is there any turnaround?

Third: from documentation...it is required that the query

@xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/Timestamp)[1]', 'nvarchar(max)')

returns a value, otherwise raise an error but in some SOAPs some fields are optionals and therefore are not always present: I tried with

;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap], default 'urn:ebay:apis:eBLBaseComponents')
insert into Test (TS,Comment) 
select if (exists @xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/Timestamp)')) then @xDoc.value('(/soap:Envelope/soap:Body/GetFeedbackResponse/Timestamp)[1]', 'nvarchar(max)')
....

but raise an error: which is correct query form?

Thanks a Lot

Joe

Upvotes: 1

Views: 1517

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Another way would be to get a name-value result set instead of columns and then you can work with that as you wish. If you want the result as columns you can for instance store the name-value result set in a temp table and do a dynamic pivot of the data.

declare 
@Root varchar(50)='GetFeedbackResponse', 
@xDoc XML = '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <ebl:RequesterCredentials soapenv:mustUnderstand="0" xmlns:ns="urn:ebay:apis:eBLBaseComponents" xmlns:ebl="urn:ebay:apis:eBLBaseComponents">
            <ebl:NotificationSignature xmlns:ebl="urn:ebay:apis:eBLBaseComponents">r6revSiTXCP9SBBFUtpDAQ==</ebl:NotificationSignature>
        </ebl:RequesterCredentials>
    </soapenv:Header>
    <soapenv:Body>
        <GetFeedbackResponse xmlns="urn:ebay:apis:eBLBaseComponents">
            <Timestamp>2015-09-06T11:20:48.528Z</Timestamp>
            <Ack>Success</Ack>
            <CorrelationID>428163922470</CorrelationID>
            <Version>899</Version>
            <Build>E899_INTL_APIFEEDBACK_17278558_R1</Build>
            <NotificationEventName>Feedback</NotificationEventName>
            <RecipientUserID>ebay_bestseller</RecipientUserID>
            <EIASToken>nY+sHZ2PrBmdj6wVnY+sEWDETj2dj6AFlIajDpaEpAydj6x9nY+seQ==</EIASToken>
            <FeedbackDetailArray>
                <FeedbackDetail>
                    <CommentingUser>ebay_bestseller</CommentingUser>
                    <CommentingUserScore>42425</CommentingUserScore>
                    <CommentText>Great buyer - We Would Appreciate 5 STARS for Our Feedback!</CommentText>
                    <CommentTime>2015-09-06T11:20:45.000Z</CommentTime>
                    <CommentType>Positive</CommentType>
                    <ItemID>310541589307</ItemID>
                    <Role>Buyer</Role>
                    <FeedbackID>1064451206013</FeedbackID>
                    <TransactionID>549674542021</TransactionID>
                    <OrderLineItemID>310541589307-549674542021</OrderLineItemID>
                </FeedbackDetail>
            </FeedbackDetailArray>
            <FeedbackDetailItemTotal>1</FeedbackDetailItemTotal>
            <FeedbackScore>126</FeedbackScore>
            <PaginationResult>
                <TotalNumberOfPages>1</TotalNumberOfPages>
                <TotalNumberOfEntries>1</TotalNumberOfEntries>
            </PaginationResult>
            <EntriesPerPage>25</EntriesPerPage>
            <PageNumber>1</PageNumber>
        </GetFeedbackResponse>
    </soapenv:Body>
</soapenv:Envelope>'

select T.X.value('local-name(.)', 'nvarchar(100)') as Name,
       T.X.value('text()[1]', 'nvarchar(100)') as Value
from @xDoc.nodes('//*[local-name(.) = sql:variable("@Root")]//*') as T(X)

local-name() returns the current node name and text() returns the node value.

nodes() shreds the XML and returns one row for each matched node in the XML.

// does a deep search of the XML

* Is a wildcard to match any nodes.

[] is used for a predicate in a xQuery expression.

sql:variable() is a function that fetches the values of variables into the xQuery. You can not use sql:variable() to build the xQuery expression.

The expression in the nodes() function will return one row for each element below GetFeedbackResponse with a deep search.

Result:

Name                      Value
------------------------- -----------------------------------------------
Timestamp                 2015-09-06T11:20:48.528Z
Ack                       Success
CorrelationID             428163922470
Version                   899
Build                     E899_INTL_APIFEEDBACK_17278558_R1
NotificationEventName     Feedback
RecipientUserID           ebay_bestseller
EIASToken                 nY+sHZ2PrBmdj6wVnY+sEWDETj2dj6AFlIajDpaEpAydj6x9nY+seQ==
FeedbackDetailArray       NULL
FeedbackDetail            NULL
CommentingUser            ebay_bestseller
CommentingUserScore       42425
CommentText               Great buyer - We Would Appreciate 5 STARS for Our Feedback!
CommentTime               2015-09-06T11:20:45.000Z
CommentType               Positive
ItemID                    310541589307
Role                      Buyer
FeedbackID                1064451206013
TransactionID             549674542021
OrderLineItemID           310541589307-549674542021
FeedbackDetailItemTotal   1
FeedbackScore             126
PaginationResult          NULL
TotalNumberOfPages        1
TotalNumberOfEntries      1
EntriesPerPage            25
PageNumber                1

Third:

You should read the docs again.

The XQuery must return at most one value.

It is perfectly fine to return no value and in that case the value returned is NULL.

Update:

If the end result you are looking for really is one row with values in columns you are better of to use what you already have figured out. It can be simplified a bit by putting the path common to all values in a nodes() clause. Something like this.

with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap], default 'urn:ebay:apis:eBLBaseComponents')
select T.X.value('(Timestamp/text())[1]', 'datetime') as Timestamp,
       T.X.value('(Ack/text())[1]', 'varchar(10)') as Ack,
       T.X.value('(CorrelationID/text())[1]', 'varchar(20)') as CorrelationID
from @xDoc.nodes('/soap:Envelope/soap:Body/GetFeedbackResponse') as T(X)

Just add the extra columns you actually need. I have also added /text() to where you extract a value from a node. For you XML it will give the exact same result as you already have only the optimizer can exclude a couple of operators from the query plan so it is potentially faster doing the shredding this way.

Upvotes: 1

Related Questions