May 6, 2012 at 5:06 pm
Here is an code example of my problem....
My question is How do I get the scores? ie: 50, 300, 710?
DECLARE @doc INT
DECLARE @VarcharArg VARCHAR(1000)
SET @VarcharArg =
'<DECISION>
<decision code="A">Approved</decision>
<decisionTimestamp>2012-05-02 12:22:28</decisionTimestamp>
<recommendedLoanAmount>0.0</recommendedLoanAmount>
<maxLoanAmount>0.0</maxLoanAmount>
<REASONS />
<PROPERTIES />
<SCORES>
<score type="CLV:CVI">50</score>
<score type="CLV:INQ">300</score>
<score type="CLV:IDV">710</score>
</SCORES>
</DECISION>
</CONTENT>'
EXEC sp_xml_preparedocument @doc OUTPUT, @VarcharArg
SELECT *
FROM OPENXML (@doc, '/DECISION/SCORES/score',1)
WITH ([type] varchar(50), score varchar(50))
Here is the output I get with the statement from above.
typescore
CLV:CVINULL
CLV:INQNULL
CLV:IDVNULL
How do I get the scores? ie: 50, 300, 710?
Any help would be greatly appreciated, Thanks in advance...
May 7, 2012 at 1:48 am
The with clause can have a third part that describes the location of the node. If you'll add it, you'll be able to get the data that you want. By the way, with SQL Server 2008 you already have XQuery. Maybe it's time to start using it instead of forxml?
DECLARE @doc INT
DECLARE @VarcharArg VARCHAR(1000)
SET @VarcharArg =
'<DECISION>
<decision code="A">Approved</decision>
<decisionTimestamp>2012-05-02 12:22:28</decisionTimestamp>
<recommendedLoanAmount>0.0</recommendedLoanAmount>
<maxLoanAmount>0.0</maxLoanAmount>
<REASONS />
<PROPERTIES />
<SCORES>
<score type="CLV:CVI">50</score>
<score type="CLV:INQ">300</score>
<score type="CLV:IDV">710</score>
</SCORES>
</DECISION>'
--</CONTENT>'
EXEC sp_xml_preparedocument @doc OUTPUT, @VarcharArg
SELECT *
FROM OPENXML (@doc, '/DECISION/SCORES/score')
WITH ([type] varchar(50) '@type',
score int '.')
exec sp_xml_removedocument @doc
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 7, 2012 at 8:09 am
Thank you so much for your help with this!!!
I will also take a look at XQuery as an option as well.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply