Parse XML within my T-SQL?

25 Sep 2007 9:12 AM

C

Hi,

I have some XML stored as a string in my SQL Server 2005 Database.

I want to extract the LGD field below as part of my query.

How can I do this in T-SQL?

<Calculation>
<Result>
<EngineInfo>
   <ModelDataVersion>14</ModelDataVersion>
</EngineInfo>
<Output>
   <EAD>2260586.523</EAD>
   <LGD>0.018</LGD>
</Output>
</Result>
</Calculation>

25 Sep 2007 10:44 AM

Pawel Potasinski

Assuming that you store data in a column of data type XML you can use the
code below.

CREATE TABLE #Temp (col xml)

INSERT #Temp (col)
SELECT '<Calculation>
<Result>
<EngineInfo>
   <ModelDataVersion>14</ModelDataVersion>
</EngineInfo>
<Output>
   <EAD>2260586.523</EAD>
   <LGD>0.018</LGD>
</Output>
</Result>
</Calculation>'

SELECT T.c.value('LGD[1]','decimal(8,3)')
FROM #Temp
CROSS APPLY col.nodes('/Calculation/Result/Output') AS T(c)

If somehow you store your xml data in (n)varchar or (n)text column (why
would you?) you can start with casting this column to xml data type.

--
Regards
Pawel Potasinski
[http://www.potasinski.pl]

Show quoteHide quote

U¿ytkownik "C" <C@discussions.microsoft.com> napisa³ w wiadomo¶ci
news:B8977E0B-7AF5-4872-9DDE-E4A836DEB68D@microsoft.com...
> Hi,
>
> I have some XML stored as a string in my SQL Server 2005 Database.
>
> I want to extract the LGD field below as part of my query.
>
> How can I do this in T-SQL?
>
> <Calculation>
> <Result>
> <EngineInfo>
>   <ModelDataVersion>14</ModelDataVersion>
> </EngineInfo>
> <Output>
>   <EAD>2260586.523</EAD>
>   <LGD>0.018</LGD>
> </Output>
> </Result>
> </Calculation>
>

26 Sep 2007 4:01 PM

Mike C#

"Pawel Potasinski" <pawel.potasin***@gmail.com> wrote in message
news:%23NDd$D2$HHA.3716@TK2MSFTNGP03.phx.gbl...
> If somehow you store your xml data in (n)varchar or (n)text column (why
> would you?) ...

....Because regulatory requirements might demand that you store an exact
character for character copy of all XML data received...

26 Sep 2007 6:17 PM

Joe Fawcett

It might be easier to use the value method:
DECLARE @Data XML;
SET @Data =
'<Calculation>
<Result>
<EngineInfo>
<ModelDataVersion>14</ModelDataVersion>
</EngineInfo>
<Output>
<EAD>2260586.523</EAD>
<LGD>0.018</LGD>
</Output>
</Result>
</Calculation>';
SELECT @Data.value('(/*/Result/Output/LGD)[1]', 'NVARCHAR(10)') LGD;

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

Show quoteHide quote

"Pawel Potasinski" <pawel.potasin***@gmail.com> wrote in message
news:%23NDd$D2$HHA.3716@TK2MSFTNGP03.phx.gbl...
> Assuming that you store data in a column of data type XML you can use the
> code below.
>
> CREATE TABLE #Temp (col xml)
>
> INSERT #Temp (col)
> SELECT '<Calculation>
> <Result>
> <EngineInfo>
>   <ModelDataVersion>14</ModelDataVersion>
> </EngineInfo>
> <Output>
>   <EAD>2260586.523</EAD>
>   <LGD>0.018</LGD>
> </Output>
> </Result>
> </Calculation>'
>
> SELECT T.c.value('LGD[1]','decimal(8,3)')
> FROM #Temp
> CROSS APPLY col.nodes('/Calculation/Result/Output') AS T(c)
>
> If somehow you store your xml data in (n)varchar or (n)text column (why
> would you?) you can start with casting this column to xml data type.
>
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
>
> U¿ytkownik "C" <C@discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:B8977E0B-7AF5-4872-9DDE-E4A836DEB68D@microsoft.com...
>> Hi,
>>
>> I have some XML stored as a string in my SQL Server 2005 Database.
>>
>> I want to extract the LGD field below as part of my query.
>>
>> How can I do this in T-SQL?
>>
>> <Calculation>
>> <Result>
>> <EngineInfo>
>>   <ModelDataVersion>14</ModelDataVersion>
>> </EngineInfo>
>> <Output>
>>   <EAD>2260586.523</EAD>
>>   <LGD>0.018</LGD>
>> </Output>
>> </Result>
>> </Calculation>
>>
>
>