Re: Shred XML Data into Columns/Rows

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <joefawcett@xxxxxxxxxxxxxxxx> wrote in message
news:%238Z58w7TJHA.3648@xxxxxxxxxxxxxxxxxxxxxxx
Are the attribute names fixed or could they change from Error to Error?

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Ganesh Muthuvelu" <GaneshMuthuvelu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:E0EB427A-2988-4A06-942B-8060E37CD8C5@xxxxxxxxxxxxxxxx
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>
******************




.

Relevant Pages

  • Shred XML Data into Columns/Rows
    ... the ErrorMessage node has several ... Can someone help me to get this using SQL Server XML capaiblities?. ... <AddPartitionResponse xmlns="http://xyz.com/WebServices/"</a>;> ...
    (microsoft.public.sqlserver.xml)
  • Re: Shred XML Data into Columns/Rows
    ... Joe Fawcett (MVP - XML) ... the ErrorMessage node has several ... <AddPartitionResponse xmlns="http://xyz.com/WebServices/"</a>;> ...
    (microsoft.public.sqlserver.xml)