Shred XML Data into Columns/Rows

If the attribute names are fixed then this might get you started:
DECLARE @Xml xml;

SET @Xml = '<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Body>

<AddPartitionResponse xmlns="http://xyz.com/WebServices/">

<AddPartitionResult>

<Success>false</Success>

<NumberOfBatches>1</NumberOfBatches>

<NumberOfEntities>2</NumberOfEntities>

<RecordsAffected>0</RecordsAffected>

<XmlRequestID>169</XmlRequestID>

<ResponseMessage>Request executed but with errors</ResponseMessage>

<ErrorMessage>

<Error Action="AddPartition" Id="2" Key="MCL_12" MultiRefId="id2"

Batch="1/1" Status="Failed"

FaultCodeNamespace="http://schemas.xmlsoap.org/soap/envelope/"

FaultCodeException="Server.generalException">ERR_REC_EXISTSFailed </Error>

<Error Action="AddPartition" Id="1" Key="MCL_11" MultiRefId="id1"

Batch="1/1" Status="Failed"

FaultCodeNamespace="http://schemas.xmlsoap.org/soap/envelope/"

FaultCodeException="Server.generalException">ERR_REC_EXISTSFailed </Error>

</ErrorMessage>

</AddPartitionResult>

</AddPartitionResponse>

</soap:Body>

</soap:Envelope>';

;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' as soap,

DEFAULT 'http://xyz.com/WebServices/')

SELECT

e.value('@Action', 'nvarchar(50)') [Action],

e.value('@Id', 'nvarchar(50)') [Id],

e.value('@Key', 'nvarchar(50)') [Key],

e.value('@MultiRefId', 'nvarchar(50)') [MultiRefId],

e.value('@Batch', 'nvarchar(50)') [Batch],

e.value('@Status', 'nvarchar(50)') [Status],

e.value('@FaultCodeNamespace', 'nvarchar(80)') [FaultCodeNamespace],

e.value('@FaultCodeException', 'nvarchar(80)') [FaultCodeException],

e.value('.', 'nvarchar(50)') [Message]

FROM
@Xml.nodes('/soap:Envelope/soap:Body/AddPartitionResponse/AddPartitionResult/ErrorMessage/Error')
X(e);






--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name



"Joe Fawcett" wrote in message

> Are the attribute names fixed or could they change from Error to Error?
>
> --
>
> Joe Fawcett (MVP - XML)
>
> http://joe.fawcett.name
>
> "Ganesh Muthuvelu" wrote in
> message
>> Hello,
>> I have a XML as below. As you may see, the ErrorMessage node has several
>> "Error" nodes with attributes and data. Now, I want to get those
>> attribute
>> names as "columns" and the values in "rows".. (Action, Key, MultiRefId,
>> Batch... are the columns).
>>
>> Can someone help me to get this using SQL Server XML capaiblities?.
>> Thanks.
>>
>>
>> ******************
>> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
>> <soap:Body>
>> <AddPartitionResponse xmlns="http://xyz.com/WebServices/">
>> <AddPartitionResult>
>> <Success>false</Success>
>> <NumberOfBatches>1</NumberOfBatches>
>> <NumberOfEntities>2</NumberOfEntities>
>> <RecordsAffected>0</RecordsAffected>
>> <XmlRequestID>169</XmlRequestID>
>> <ResponseMessage>Request executed but with
>> errors</ResponseMessage>
>> <ErrorMessage>
>> <Error Action="AddPartition" Id="2" Key="MCL_12"
>> MultiRefId="id2"
>> Batch="1/1" Status="Failed"
>> FaultCodeNamespace="http://schemas.xmlsoap.org/soap/envelope/"
>> FaultCodeException="Server.generalException">ERR_REC_EXISTSFailed
>> </Error>
>> <Error Action="AddPartition" Id="1" Key="MCL_11"
>> MultiRefId="id1"
>> Batch="1/1" Status="Failed"
>> FaultCodeNamespace="http://schemas.xmlsoap.org/soap/envelope/"
>> FaultCodeException="Server.generalException">ERR_REC_EXISTSFailed
>> </Error>
>> </ErrorMessage>
>> </AddPartitionResult>
>> </AddPartitionResponse>
>> </soap:Body>
>> </soap:Envelope>
>> ******************
>
>
 >> Stay informed about: Shred XML Data into Columns/Rows