January 20, 2014 at 2:16 pm
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 */
F1068F1127F1148F1155 F1152F65F02
SALEKiyana303973Stevenson, Shakerra3NULLNULL
SALEKiyana303973Stevenson, Shakerra3NULLNULL
SALEKiyana303973Stevenson, Shakerra3NULLNULL
SALEKiyana303973Stevenson, Shakerra3NULLNULL
January 20, 2014 at 7:49 pm
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'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply