May 12, 2010 at 7:25 am
I posted this in Newbies but have not received an answer and was wondering if this forum group might help.
I have 4 tables of XML CLOB data entered by users in the transactional database.
The XML CLOB Schema is defined as
--<java>
-- <object>
-- <void>
-- <string></string> -- field name [1]
-- <string></string> -- field value [2]
-- </void>
-- </object>
--</java>
For each row there are n number of <string> node pairs. Some field values are null and use the xml node <string /> (makes usiing type() difficult)
I can get the count of the voids per row using:
SELECT
X_CLOB.query ('count(//void)')
FROM XML_CUSTOMER_VALUE_SET
Using the table name, schema, and xml column name in this table, how can I :
loop through the void node pairs n times per row per number of rows?
xml.value('(//void[1..n]/string) [1]', 'varchar(max)')
xml.value('(//void[1..n]/string) [2]', 'varchar(max)')
What I want to achieve as the output is:
ROW # FIELD NAME 1 ... FIELD NAME n
---------------------------------------------
FIELD VALUE 1 .. FIELD VALUE n
I would very much appreciate a solution. Performance is not an issue.
May 12, 2010 at 10:34 am
Maybe the reason you didn't get any reply yet is the way you posted your question.
There are some people around (including myself) that prefer to have ready to use sample data together with exptected result based on those samples.
At least the latter is missing. 😉
May 12, 2010 at 12:08 pm
I'm not sure I understand your comment. There is sample data attached to my entry.
RTM
May 12, 2010 at 12:33 pm
Please re-read my previous post: I asked for your expected result based on your sample data. I can't find it...
All I see is
ROW # FIELD NAME 1 ... FIELD NAME n
---------------------------------------------
FIELD VALUE 1 .. FIELD VALUE n
which has nothing to do with your sample data.
May 12, 2010 at 12:36 pm
Mike,
I think what Lutz is trying to say is that we are happy to try and code something, but it would help if you had the results listed, so some sample results that would be shown as in
ROW # FIELD NAME 1 ... FIELD NAME n
---------------------------------------------
1 5 6
that is relevant based on sample data. So if you have 2 items for field 1, we can see what we should check against.
In terms of sample data, it helps if you have a T-SQL ready document we can use. Something like
CREATE TABLE [dbo].[XmlImportTest](
[xml_data] [xml] NULL
)
-- I add the following data in:
insert into dbo.XmlImportTest (xml_data)
values
('<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>')
Which we can paste into SSMS and work with. Having a text file, especially a large one, isn't something we can quickly set up. Since we're volunteering time, trying to answer something on a break, the setup and verification time matters.
May 12, 2010 at 3:11 pm
Thanks Steve and I do appreciate the time it takes to set things up.
Guess I was not clear enough and my need is very basic (hence the reason I posted first in Newbie)
We have a table full of XML formatted data and we are moving away from storing the data in XML. I am looking for was appropriate looping structure to traverse all the string nodes in each row to extract the data which will eventually be used to populate tables.
Right now, all that is needed is output that we can save.
The number of void nodes varies per row so I depend on a query to give me the total count per row
The attachment from the first post is one row of data from the XML_CUSTOMER_VALUE_SET
So it would go something like this:
@rCnt =
(SELECT
CUSTOMER_ID,
X_CLOB.query('count(java/object/void)').value('.','int')
FROM XML_CUSTOMER_VALUE_SET)
• For every row (rCnt) in the table
• Loop for each void node @cnt in a row
BEGIN
SELECT
x_clob.query('/java/object/void[position()=sql:variable("@cnt")]/string[1]').value('.','varchar(100)'),
x_clob.query('/java/object/void[position()=sql:variable("@cnt")]/string[2]').value('.','varchar(100)')
FROM
XML_CUSTOMER_VALUE_SET
@cnt=@cnt+1
WHERE CUSTOMER_ID = @rCnt
@rCnt=rCnt+1
End
The output can be in either horizontal or verticle.
Would someone be able to shed some light on settting this up correctly?
May 12, 2010 at 4:22 pm
If I understand correctly, I don't much like the idea of using the values in the xml as column names....
If however, you just want to get the xml data out into a "details" table which can be joined to your existing table....
--== Set up some test data
declare @xml xml = '<java version="1.5.0_12" class="java.beans.XMLDecoder">
<object class="java.util.HashMap">
<void method="put">
<string>Product[0].Account Information[0].Similar Account Flag</string>
<string>false</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Company Information[0].Product</string>
<string>GMAC</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Account City</string>
<string>Miami</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Account State</string>
<string>FL</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].No Duplicates Add Account</string>
<string>0</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Ignore Conflict Account</string>
<string>false</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Insured Information[0].Insured State</string>
<string>FL</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Insured Information[0].Insured City</string>
<string>Miami</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Insured Information[0].Named Insured</string>
<string>1000 Venetian Way Condominium Association Inc</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].Account Name</string>
<string>1000 Venetian Way Condominium Association Inc</string>
</void>
<void method="put">
<string>Product[0].Account Information[0].GMAC Account Imported Flag</string>
<string/>
</void>
</object>
</java>'
declare @t table (rowid int identity(1,1) primary key,xmlData xml)
insert @t(xmlData)
select @xml union all
select @xml
--= and extract it to form a "details" table
select
t.rowid
,xmlData.row.value('./string[1]','varchar(512)') as FieldName
,xmlData.row.value('./string[2]','varchar(512)') FieldValue
from @t as t
cross apply t.xmlData.nodes('//void') xmlData(row)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 12, 2010 at 10:47 pm
Does the reply posted by mister.magoo solve the issue?
.
May 13, 2010 at 7:13 am
Mr Magoo,
Thanks for the reply and I appreciate the input.
It still seems that I have not phrased my question in a way that everyone gets.
The great news is that I figured it out the solution to extract all of the data out of XML structures.
Consider the matter closed.
Cheers to everyone who looked, considered, and answered.
May 13, 2010 at 7:16 am
It would be great if you could post an update, Mike. The next person might have a similar issue.
May 19, 2010 at 11:19 am
[font="Courier New"]Here's the solution that worked for me:
Let's recap first ... the XML is stored in an OLTP database in a table called SUBMISSION_VALUE_SET
The XML Schema is:
<java>
<object>
<void>
<string></string> -- UI Field Name
<string></string> -- UI Field Value
</void>
</object>
</java>
Per row, there are between 11-590 void nodes and over 18,000 rows
Business need: Extract the UI Field Name and Field Value per <void> node per row.
Setting up the LOOP Variables and Counters
--PRIMARY KEY (XML CLOB Table) START--
DECLARE @rowCNT INT
SELECT @rowCNT = 20719
--MAX ROWS-----------
DECLARE @MaxRowCNT INT
SELECT @MaxRowCNT = (SELECT MAX(SUBMISSION_ID) FROM SUBMISSION_VALUE_SET)
--FIRST Row of CLOB Data------
DECLARE @XML XML
SELECT @XML = (SELECT CONVERT(XML,SUBMISSION_CLOB)
FROM dbo.SUBMISSION_VALUE_SET WHERE SUBMISSION_ID=@RowCNT)
--XML NODE COUNTER START------
DECLARE @NodeCNT INT
SELECT @NodeCNT =1
--MAX NODES on ROW---------
DECLARE @MaxNODES INT
SELECT @MaxNODES = (SELECT @XML.query('count(java/object/void)').value('.','int'))
Let's go get the data ...
WHILE @RowCNT <= @MaxRowCNT
BEGIN
WHILE
@NodeCNT <= @MaxNODES
BEGIN
SELECT
@RowCNT,
@XML.value('(java/object/void[position()=sql:variable("@NodeCNT")]/string) [1]','varchar(max)'),
@XML.value('(java/object/void[position()=sql:variable("@NodeCNT")]/string) [2]','varchar(max)')
SET @NodeCNT=@NodeCNT+1
END
SELECT @RowCNT = @RowCNT+1
SELECT @XML = (SELECT CONVERT(XML,SUBMISSION_CLOB) FROM dbo.SUBMISSION_VALUE_SET WHERE SUBMISSION_ID=@RowCNT)
SELECT @MaxNODES = (SELECT @XML.query('count(java/object/void)').value('.','int'))
SELECT @NodeCNT =1
END[/font]
This was a particularly large output of 4 columns x 2,567,145 rows. My original solution was to use XMLOPEN but it didn't work because of null nodes and I wanted to retain both data and null/blanks for Field Values (see schema at top)
May 19, 2010 at 11:45 am
If anyone is wondering why I used the CONVERT function, the XML is stored in a column that was typed as "TEXT"...
Needless to say, it isn't "proper" XML and needed to be converted before applying the XQuery functions ...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply