June 20, 2009 at 7:05 am
Hello,
Is it possible to get child & parent element in the same recordset while reading the data from an XML file while using OPENXML?
XML file
Parent
Child1
Grand child1
Grand Child2
Child2
Is it possible to extract child1 and Grand child1 and Grand child2 in the same recordset.
Please let me know if there are any alternate ways for it too.
Thanks in advance.
Regards,
Rohit
June 20, 2009 at 7:15 am
Would you please post sample data and expected results together with the code you've tried so far?
To post xml code please use [ code="xml" ] tag.
Also, why do you specifically ask for openxml? What SQL version are you using?
If you're running SS2K5 or SS2K8 you should look into XQuery instead...
June 22, 2009 at 11:39 am
Hi,
Thanks for the reply. I have XML file like the one mentioned below.
0
INFO
0
INFO
9999999
username
A product name
sample
some name
99230203
1924829392
US
XYZ CORPORATION
20090622
20090522
400
044569531
XYZ Corporation
Alias1
alias2
alias3
Other data
In this file one ID_NBR basically represents data for a company (PK). Details about the company will be present in MON_PROD_RS tag.
Any change in the information about the company then it is tagged with tag. I want to extract the data as a recordset which will contain company data (MON_PROD_RS) and wit alerts (NTFCRS).
Currently I am importing the XML file into SQL 2K5 table using openrowset (into XML column) and then trying to convert it into SQL table.But I am not able to extract MON_PROD_RS and NTFCRS in the same table.
Here Mon_prod_rs and NTFCRS are at the same level (i.e. children of REGNRS).
I m too new to XML processing. Please help.
Thanks
Rohit
June 22, 2009 at 12:44 pm
Based on your XML code above, what does your expected results look like?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 22, 2009 at 1:09 pm
Hi Rohit,
as already mentioned by Wayne you should let us know what your expected result looks like. It will help us to help you. Otherwise you'll get results that might not be exactly what you're looking for (e.g. the following sample code).
If you'd like or you need to ge more familiar with xml and SS2K5 I recommend you go through the article series by J. Sebastian ("XML Workshop", search for it on this forum and you'll get more than 20 articles covering almost every subject related to xml and SQL Server).
Back to your question:
Following please find an example on how to deal with xml data using XQuery instead of openxml.
I attached two options: one to query the data from a table and the other one using xml variable directly.
The "trick" on both of them is to use the query method to get the elements from the NTFCRS node.
Based on the sample you should be able to get the elements you need.
DECLARE @xml XML
SELECT @xml='
0
INFO
0
INFO
9999999
username
A product
sample
some name
99230203
1924829392
US
XYZ CORPORATION
20090622
20090522
400
044569531
XYZ Corporation
Alias1
alias2
alias3
Other data
'
-- option 1: using a table to query the data
DECLARE @t TABLE (col XML)
INSERT INTO @t SELECT @XML
SELECT
t.c.value('../CUST_ACCT_ID[1]','VARCHAR(30)') AS CUST_ACCT_ID ,
t.c.value('ID_NBR[1]','VARCHAR(30)') AS ID ,
t.c.value('Name[1]','VARCHAR(30)') AS Name ,
t.c.value('Alias[1]','VARCHAR(30)') AS alias1 ,
t.c.value('Alias[2]','VARCHAR(30)') AS alias2,
t.c.value('Alias[3]','VARCHAR(30)') AS alias3,
t.c.query('../NTFCRS/DETECT_DT[1]').value('.','varchar(60)') AS DETECT_DT,
t.c.query('../NTFCRS/SRC_DT[1]').value('.','varchar(60)') AS SRC_DT,
t.c.query('../NTFCRS/TOT_PMT[1]').value('.','varchar(60)') AS TOT_PMT
FROM @t
CROSS APPLY
col.nodes ('DGX/GLBLMNSVCMSGSRSV1/GETNTFCTRNRS/GETNTFCRS/USERRS/PFLRS/REGNRS/MON_PROD_RS') AS t(c)
-- option 2: query the data directly from the xml variable
SELECT
t.c.value('../CUST_ACCT_ID[1]','VARCHAR(30)') AS CUST_ACCT_ID ,
t.c.value('ID_NBR[1]','VARCHAR(30)') AS ID ,
t.c.value('Name[1]','VARCHAR(30)') AS Name ,
t.c.value('Alias[1]','VARCHAR(30)') AS alias1 ,
t.c.value('Alias[2]','VARCHAR(30)') AS alias2,
t.c.value('Alias[3]','VARCHAR(30)') AS alias3,
t.c.query('../NTFCRS/DETECT_DT[1]').value('.','varchar(60)') AS DETECT_DT,
t.c.query('../NTFCRS/SRC_DT[1]').value('.','varchar(60)') AS SRC_DT,
t.c.query('../NTFCRS/TOT_PMT[1]').value('.','varchar(60)') AS TOT_PMT
FROM @xml.nodes ('DGX/GLBLMNSVCMSGSRSV1/GETNTFCTRNRS/GETNTFCRS/USERRS/PFLRS/REGNRS/MON_PROD_RS') AS t(c)
/* result for both options
CUST_ACCT_IDIDNamealias1alias2alias3DETECT_DTSRC_DTTOT_PMT
99230203044569531XYZ Corporation Alias1 alias2 alias32009062220090522400
*/
June 25, 2009 at 6:10 am
Hi Lutz,
Thanks a lot. This is what exactly I was looking for. Only thing is my tags which will appear in NTFRS will be dynamic,means,any tag can appear out of 150 specfied tags under NTFRS. I am planning take care of that in SQL query once results are fetched into a table.
Thanks again for your help.
Regards,
Rohit
June 25, 2009 at 1:27 pm
How are you planning to store the 150 tags?
Are you planning on creating a table with 150 cols or are you going to store the NTFRS data in a separate table with a structure like [CUST_ACCT_ID],[ID_NBR],[NTFCRS_TAG_NAME],[NTFCRS_TAG_VALUE] having up to 150 rows per customer?
I'd recommend to go for the 2nd option.
In that case you'd need to separate the xml-query for the NTFCRS nodes. I've been playing around with it a little bit and would do it as follows (results based on your sample data as before):
-- step 1: same as before except for the NTFRS query part
SELECT
t.c.value('../CUST_ACCT_ID[1]','VARCHAR(30)') AS CUST_ACCT_ID ,
t.c.value('ID_NBR[1]','VARCHAR(30)') AS ID ,
t.c.value('Name[1]','VARCHAR(30)') AS Name ,
t.c.value('Alias[1]','VARCHAR(30)') AS alias1 ,
t.c.value('Alias[2]','VARCHAR(30)') AS alias2,
t.c.value('Alias[3]','VARCHAR(30)') AS alias3
FROM @xml.nodes ('DGX/GLBLMNSVCMSGSRSV1/GETNTFCTRNRS/GETNTFCRS/USERRS/PFLRS/REGNRS/MON_PROD_RS') AS t(c)
/* result set
CUST_ACCT_IDIDNamealias1alias2alias3
99230203044569531XYZ Corporation Alias1 alias2 alias3
*/
-- step 2: create intermediate table to hold information from the NTFRS parent element and the NTFRS data in xml format
DECLARE @t TABLE (data XML, cust INT, id INT)
-- step 3: fill intermediate table
INSERT INTO @t
SELECT t.c.query('NTFCRS/*') AS data, -- take all sub-nodes from NTFCRS per customer and store as xml
t.c.value('CUST_ACCT_ID[1]','VARCHAR(30)') AS CUST_ACCT_ID ,
t.c.value('ID_NBR[1]','VARCHAR(30)') AS ID
FROM @xml.nodes('/DGX/GLBLMNSVCMSGSRSV1/GETNTFCTRNRS/GETNTFCRS/USERRS/PFLRS/REGNRS') t(c)
-- step 4: extract xml node name and value per customer from intermediate table
SELECT
subqry.cust,
subqry.id,
T.c.value('localname[1]', 'varchar(100)') AS element, -- node name extracted from data column (holding NTFRS elements in xml format)
T.c.value('value[1]', 'varchar(100)') AS val -- node value extracted from data column
FROM
(SELECT
S.c.query('
for $node in /descendant::node()[local-name() != ""]
return
{ local-name($node) }
{ $node }
') AS nodes,
t.cust AS cust,
t.id AS id
FROM @t t
CROSS APPLY data.nodes('.') S(c)
) subqry --returns content of intermediate table with each xml node and value separated, but still in xml format
CROSS APPLY subqry.nodes.nodes('/node') AS T(c) -- anchor for extracting the values of the xml data
/* result set
custidelementval
992302031924829392DETECT_DT20090622
992302031924829392SRC_DT20090522
992302031924829392TOT_PMT400
*/
Note: I didn't find a way to do it without using the intermediate table... But at least this version is independent of the number of nodes within the NTFCRS.
June 26, 2009 at 1:10 am
Hi Lutz,
I was planning to create a table with all 150 elements and insert them when the XML file is received.
I think the second option, suggested by you, is better one.
Regards,
Rohit
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply