XML Parsing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

XML Parsing

Author

Topic

mahesh_iiim
Starting Member

2 Posts

Posted - 2010-03-26 : 11:42:11

I am trying to parse the below XML using OPENXML in SQL Server 2000. It's doing good apart from first 2 columns 'type' and 'perioddate' are not changing their values for different XML node. Any help is much appreciated.

DECLARE @xml_text VARCHAR(8000), @i INT

SELECT @xml_text = '
<?xml version="1.0" encoding="UTF-8"?>
<period>
<table type="Interest Rate">
<perioddate>05/02/2001</perioddate>
<row type="coldesc">
<cell id="tl" bold="1" cspan="c1">Loan Amount1</cell>
<cell id="c605" bold="2" abbr=" percentage Annual Percentage Rate" cspan="c2">% APR</cell>
</row>
<row>
<cell id="r593" bold="0" cspan="1">#163;5,000 +</cell>
<cell bold="1">28.0</cell>
</row>
</table>
<table type="Repayment">
<perioddate>05/01/2002</perioddate>
<row type="coldesc1">
<cell id="tl" bold="3" cspan="c3">Loan Amount2</cell>
<cell id="c525" bold="4" cspan="c4">1,000</cell>
</row>
</table>
</period>
'

EXEC SP_XML_PREPAREDOCUMENT @i OUTPUT, @xml_text

SELECT type,
perioddate,
type2 as type2,
bold,
cspan,
abbr,
cell
FROM OPENXML(@i, '/period/table/row/cell', 1)
WITH (
type VARCHAR(100) '/period/table/@type',
perioddate VARCHAR(100) '/period/table/perioddate',
type2 VARCHAR(100) '../@type',
bold VARCHAR(100),
cspan VARCHAR(100),
abbr VARCHAR(100),
cell VARCHAR(100) 'text()'
)

EXEC SP_XML_REMOVEDOCUMENT @i

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 01:22:25

[code]
DECLARE @xml_text VARCHAR(8000), @ixml int
SELECT @xml_text = '
<?xml version="1.0" encoding="UTF-8"?>
<period>
<table type="Interest Rate">
<perioddate>05/02/2001</perioddate>
<row type="coldesc">
<cell id="tl" bold="1" cspan="c1">Loan Amount1</cell>
<cell id="c605" bold="2" abbr=" percentage Annual Percentage Rate" cspan="c2">% APR</cell>
</row>
</table>
<table type="Repayment">
<perioddate>05/01/2002</perioddate>
<row type="coldesc1">
<cell id="tl" bold="3" cspan="c3">Loan Amount2</cell>
<cell id="c525" bold="4" cspan="c4">1,000</cell>
</row>
</table>
</period>
'

EXEC sp_xml_preparedocument @ixml OUTPUT, @xml_text

SELECT *
FROM
(
SELECT *
FROM OPENXML (@ixml,'/period/table')
WITH ([type] varchar(100) './@type',
perioddate datetime './perioddate',
[rowtype] varchar(200) './row/@type'
)
)t
INNER JOIN
(
SELECT *
FROM OPENXML (@ixml,'/period/table/row/cell')
WITH (id varchar(10) '@id',
cell varchar(100) '.',
abbr varchar(100) '@abbr',
[rowtype] varchar(200) '../@type'
)
)t1
ON t1.[rowtype]=t.[rowtype]

EXEC sp_xml_removedocument @ixml

output
------------------------------------------
type perioddate rowtype id cell abbr rowtype
Interest Rate 2001-05-02 00:00:00.000 coldesc tl Loan Amount1 NULL coldesc
Interest Rate 2001-05-02 00:00:00.000 coldesc c605 % APR percentage Annual Percentage Rate coldesc
Repayment 2002-05-01 00:00:00.000 coldesc1 tl Loan Amount2 NULL coldesc1
Repayment 2002-05-01 00:00:00.000 coldesc1 c525 1,000 NULL coldesc1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahesh_iiim
Starting Member

2 Posts

Posted - 2010-03-29 : 07:16:55

Thanks visakh for your reply.
But some of my 'row' tag don't have 'type' attribute, so these records will not pick using the inner join.
In the sample XML I am interested in those records which have 'table type' = 'Interest Rate'. Any direct query for this?

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:33:46

I dont think there's a direct way to do this in SQL 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources