Shred XML Data into Columns/Rows - XSL - XML

#1 (**permalink**) 11-25-2008, 11:37 PM

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/envelo pe/">
<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/soa p/envelope/"
FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed </Error>
<Error Action="AddPartition" Id="1" Key="MCL_11" MultiRefId="id1"
Batch="1/1" Status="Failed"
FaultCodeNamespace="http://schemas.xmlsoap.org/soa p/envelope/"
FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed </Error>
</ErrorMessage>
</AddPartitionResult>
</AddPartitionResponse>
</soap:Body>
</soap:Envelope>
******************

#2 (**permalink**) 11-26-2008, 12:14 PM

Are the attribute names fixed or could they change from Error to Error?

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Ganesh Muthuvelu" <GaneshMuthuvelu@discussions.microsoft.com> wrote in
message news:E0EB427A-2988-4A06-942B-8060E37CD8C5@microsoft.com...
> 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/envelo pe/">
> <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/soa p/envelope/"
> FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed </Error>
> <Error Action="AddPartition" Id="1" Key="MCL_11" MultiRefId="id1"
> Batch="1/1" Status="Failed"
> FaultCodeNamespace="http://schemas.xmlsoap.org/soa p/envelope/"
> FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed </Error>
> </ErrorMessage>
> </AddPartitionResult>
> </AddPartitionResponse>
> </soap:Body>
> </soap:Envelope>
> ******************

#3 (**permalink**) 11-26-2008, 02:16 PM

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/envelo pe/">

<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/soa p/envelope/"

FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed </Error>

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

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

FaultCodeNamespace="http://schemas.xmlsoap.org/soa p/envelope/"

FaultCodeException="Server.generalException">ERR_R EC_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/AddPartitionR esponse/AddPartitionResult/ErrorMessage/Error')
X(e);

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Joe Fawcett" <joefawcett@newsgroup.nospam> wrote in message
news:%238Z58w7TJHA.3648@TK2MSFTNGP05.phx.gbl...
> Are the attribute names fixed or could they change from Error to Error?
>
> --
>
> Joe Fawcett (MVP - XML)
>
> http://joe.fawcett.name
>
> "Ganesh Muthuvelu" <GaneshMuthuvelu@discussions.microsoft.com> wrote in
> message news:E0EB427A-2988-4A06-942B-8060E37CD8C5@microsoft.com...
>> 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/envelo pe/">
>> <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/soa p/envelope/"
>> FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed
>> </Error>
>> <Error Action="AddPartition" Id="1" Key="MCL_11"
>> MultiRefId="id1"
>> Batch="1/1" Status="Failed"
>> FaultCodeNamespace="http://schemas.xmlsoap.org/soa p/envelope/"
>> FaultCodeException="Server.generalException">ERR_R EC_EXISTSFailed
>> </Error>
>> </ErrorMessage>
>> </AddPartitionResult>
>> </AddPartitionResponse>
>> </soap:Body>
>> </soap:Envelope>
>> ******************
>
>