XPATH and OPENXML - 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 2005 Forums

Transact-SQL (2005)

XPATH and OPENXML

Author

Topic

ftsoft
Starting Member

22 Posts

Posted - 2009-11-11 : 13:45:51

In the following SP I get a result that I don't expect, namely the insert takes the first value and inserts that for all of the values. Maybe I don't have the correct XPATH configuration in the "WITH" statement??

The SP:

ALTER PROCEDURE dbo.open2xmltest
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
BEGIN
DECLARE @index int
DECLARE @xmltest varchar(8000)
set @xmltest =
'<DATA_RECORD>
<E01>
<E01_01>2009128</E01_01>
<E01_02>FTSoft</E01_02>
<E01_03>Tipp City IRS</E01_03>
<E01_04>V01.1</E01_04>
</E01>
<E02>
<E02_01>55-027</E02_01>
<E02_04>30</E02_04>
<E02_05>75</E02_05>
<E02_06 >135</E02_06>
<E02_06 >195</E02_06>
<E02_06 >215</E02_06>
<E02_06>215</E02_06>
<E02_07>150</E02_07>
<E02_08>225</E02_08>
<E02_09>290</E02_09>
<E02_10>360</E02_10>
<E02_12>303</E02_12>
<E02_20>390</E02_20>
</E02>
</DATA_RECORD>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmltest

CREATE TABLE OpenXmlTemp2
(E02_06 INT)

INSERT INTO OpenXMLTemp2
SELECT *
FROM OPENXML(@index, '/DATA_RECORD/E02/E02_06',2)
WITH (E02_06 INT '//E02_06')

SELECT * FROM OpenXMLTemp2

DROP TABLE OpenXMLTemp2

EXEC sp_xml_removedocument @index
END
RETURN

The result:

Running [dbo].[open2xmltest].

E02_06
-----------
135
135
135
135
(4 row(s) affected)
(4 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[open2xmltest].

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-11 : 16:16:17

if you do the select by itself it does bring in the right value as seen in last column. it must be something with your WITH


11	7	1	E02_06	NULL	NULL	NULL	10	NULL
21	11	3	#text	NULL	NULL	NULL	NULL	135
12	7	1	E02_06	NULL	NULL	NULL	11	NULL
22	12	3	#text	NULL	NULL	NULL	NULL	195
13	7	1	E02_06	NULL	NULL	NULL	12	NULL
23	13	3	#text	NULL	NULL	NULL	NULL	215
14	7	1	E02_06	NULL	NULL	NULL	13	NULL
24	14	3	#text	NULL	NULL	NULL	NULL	215

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-11 : 16:17:15

try this

SELECT *
FROM OPENXML (@index,'/DATA_RECORD/E02/E02_06',2)
WITH (E02_06 INT 'text()')

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ftsoft
Starting Member

22 Posts

Posted - 2009-11-11 : 16:47:33

Yes. This works. After looking around the web, I'm not sure exactly why though? Anyway thank you.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-11 : 17:03:39

depends where you stuff your xml values

DECLARE @index int
DECLARE @xmltest varchar(8000)
set @xmltest =
'<DATA_RECORD>
<E01>
<E01_01>2009128</E01_01>
<E01_02>FTSoft</E01_02>
<E01_03>Tipp City IRS</E01_03>
<E01_04>V01.1</E01_04>
</E01>
<E02>
<E02_01>55-027</E02_01>
<E02_04>30</E02_04>
<E02_05 value="75"/>
<E02_06 value="135"/>
<E02_06 value="195"/>
<E02_06 value="215"/>
<E02_06 value="215"/>
<E02_07>150</E02_07>
<E02_08>225</E02_08>
<E02_09>290</E02_09>
<E02_10>360</E02_10>
<E02_12>303</E02_12>
<E02_20>390</E02_20>
</E02>
</DATA_RECORD>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmltest

CREATE TABLE OpenXmlTemp2
(E02_06 INT)

INSERT INTO OpenXMLTemp2
SELECT *
FROM OPENXML (@index,'/DATA_RECORD/E02/E02_06',2)
WITH (E02_06 INT '@value')

SELECT * FROM OpenXMLTemp2
--
DROP TABLE OpenXMLTemp2

EXEC sp_xml_removedocument @index

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ftsoft
Starting Member

22 Posts

Posted - 2009-11-11 : 20:21:25

I guess I'm not clear on what exactly the "text()" is doing.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 13:17:11

if you look at the content of the xml as a rowset it think it help to understand text(). xml is very tough, I think, that is why I do not use it. but more power to you if you can figure it out.
DECLARE @index int
DECLARE @xmltest varchar(8000)
set @xmltest =
'<DATA_RECORD>
<E01>
<E01_01>2009128</E01_01>
<E01_02>FTSoft</E01_02>
<E01_03>Tipp City IRS</E01_03>
<E01_04>V01.1</E01_04>
</E01>
<E02>
<E02_01>55-027</E02_01>
<E02_04>30</E02_04>
<E02_05>75</E02_05>
<E02_06 >135</E02_06>
<E02_06 >195</E02_06>
<E02_06 >215</E02_06>
<E02_06>215</E02_06>
<E02_07>150</E02_07>
<E02_08>225</E02_08>
<E02_09>290</E02_09>
<E02_10>360</E02_10>
<E02_12>303</E02_12>
<E02_20>390</E02_20>
</E02>
</DATA_RECORD>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmltest

SELECT id, parentid, nodetype,localname,prefix,namespaceuri,datatype,prev,text
FROM OPENXML(@index, '/DATA_RECORD/E02/E02_06',2)


id                   parentid             nodetype    localname  prefix  namespaceuri datatype prev                 text
-------------------- -------------------- ----------- -------------------------------------------------------------------
11                   7                    1           E02_06     NULL    NULL         NULL     10                   NULL
21                   11                   3           #text      NULL    NULL         NULL     NULL                 135
12                   7                    1           E02_06     NULL    NULL         NULL     11                   NULL
22                   12                   3           #text      NULL    NULL         NULL     NULL                 195
13                   7                    1           E02_06     NULL    NULL         NULL     12                   NULL
23                   13                   3           #text      NULL    NULL         NULL     NULL                 215
14                   7                    1           E02_06     NULL    NULL         NULL     13                   NULL
24                   14                   3           #text      NULL    NULL         NULL     NULL                 215

and if you do this
SELECT id, parentid, nodetype,localname,prefix,namespaceuri,datatype,prev,text
FROM OPENXML(@index, '/DATA_RECORD',2)
then you will see exactly what your xml looks like as rowset. you can also see how they are all tied as if in one self referencing relational table.
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources