Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENXML : Nodes and elements Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:29 AM
Points: 5, Visits: 41
I am trying to read xml data from a SQL Table.
I found some code snippets and modify them to work with my data.

I am currently able to read the xml data column and parse out most of the elements except for what I'll call sub elements. Not sure if this is the right name.

Below I've listed a portion of my xml data that I've read into a SQL table with a column type of xml. This is followed by my current procedure that

- declares the appropriate variables
- sets the root xmlns
- calls the sp_xml_preparedocument procedure

Then I have my select statement using the OPENXML function.
As one of the parameters to the OPENXML call, I define the first three nodes (elements ?).
I'm able to retrieve attributes data from the /day/trs node
but I am unable to get the value for the F65 element in what I think is the /day/trs/r node.

Is the F65 an element or attribute? It looks to be an element but I do not know how to get it's value. Also, not sure how I would get a value from an element/attribute that is below what I've define in the OPENXML node hierarchy.

/*

XML file

<day xmlns="x-schema:..\schema_ej.xml" FILE="40119001.002">
<trs F1068="SALE" F254="2014-01-19" F253="2014-01-19" F1056="001" F1057="002"
F1035="11:34:25" F1036="11:37:26" F1032="8283" F1764="00007965" F1185="1020"
F1126="1011" F1127="Kiyana" F1148="303973" F1155="Stevenson, Shakerra" F1152="3">
<r F1101="1">
<itm F01="0001480064642" F02="HWN PNCH GRN BRRY JCE" F04="1202" F03="12" F81="1"
F79="1" F113="SALE" F1007="1.69" F1006="1" />
<F65>1.69</F65>
<F64>1</F64>
<F1263>0.1</F1263>
</r>
<r F1101="2">
<itm F01="0006414404213" F02="CBAD SPAG MTBLLS JMBO" F04="1001" F03="10" F79="1" F1007="0.99" F1006="1" />
<F65>0.99</F65>
<F64>1</F64>
<key in="06414404213" fn="710" />
</r>
<r F1101="3">
<sub F04="8002" F02="Vegetables" F03="80" F79="1" F113="" F1007="0.99" F1006="1" />
<F65>2.39</F65>
<F64>1</F64>
<key in="01600050330" fn="710" />
</r>
</trs>
</day>
*/


DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type

select @xmlDocument = convert(varchar(max),[XMLData])
FROM XMLwithOpenXML where [Id] = 7


DECLARE @rootxmlns varchar(100)
SET @rootxmlns = '<root xmlns:hm="x-schema:..\schema_ej.xml" />'

EXEC sp_xml_preparedocument @docHandle OUTPUT,
@xmlDocument
,@rootxmlns

SELECT
F1068 ,F1127
,F1148 ,F1155
,F1152 ,F65
,F02
from OPenxml(@dochandle, N'hm:day/hm:trs/hm:r',2)
WITH (
F1068 [varchar](20) '../@F1068' ,
F1127 [varchar](20) '../@F1127',
F1148 [varchar](20) '../@F1148',
F1155 [varchar](20) '../@F1155',
F1152 [varchar](20) '../@F1152',
F65 [varchar](20) 'F65',
F02 [varchar] (20) './sub/@F02'
)

EXEC sp_xml_removedocument @docHandle


/* Here is part of the output from the above query */

F1068 F1127 F1148 F1155 F1152 F65 F02
SALE Kiyana 303973 Stevenson, Shakerra 3 NULL NULL
SALE Kiyana 303973 Stevenson, Shakerra 3 NULL NULL
SALE Kiyana 303973 Stevenson, Shakerra 3 NULL NULL
SALE Kiyana 303973 Stevenson, Shakerra 3 NULL NULL
Post #1532784
Posted Monday, January 20, 2014 7:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:29 AM
Points: 5, Visits: 41
Resolved:

after investigating a little more, I was able to resolved my issue.

I needed to add the namespace name before the element name as shown below.
I was also able to return values for elements below the level defined in the OPENXML (third parameter) by also using the namespace name plus the next element down.



F65 [varchar](20) 'hm:F64',
F02 [varchar] (20) 'hm:itm/@F02'
Post #1532837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse