Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do you map to rows inside different branches of same XML document using OPENXML rowset function?...


How do you map to rows inside different branches of same XML document using OPENXML rowset function?

Author
Message
polkadot
polkadot
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
I'm able to map to one hierarchy of an XML document at a time, but I need to retrieve data sitting in other hierarchies of the same document.
In the XML below
1. Root is Joblog
2. Header node contains server, name, start_time, type, log_name
3. Media_drive_and_media_info node contains it's own set of children of which I am interested in (for example) media_mount_date.

But, with the below query, only header node info is retrieved


USE Sandbox
GO

declare @dochandle as int;
declare @xmldocument as nvarchar(max);
set @xmldocument = '
<joblog>
<job_log_version version="2.0" />
<header>
<filler />
<server>Job server: BUMMERMMS0
</server>
<name>Job name: BUMMERBUILDS\DAILYBUILDS D:-Normal Weekly Backup Policy-Weekly - FULL To LTO3
</name>
<start_time>Job started: Wednesday, September 04, 2013 at 9:17:28 AM
</start_time>
<type>Job type: Backup
</type>
<log_name>Job Log: BEX_BUMMERMMS0_00004.xml
</log_name>
<filler />
</header>
<media_mount_date>Drive and media mount requested: 9/4/2013 9:17:28 AM</media_mount_date>
<media_drive_and_media_info>
<media_mount_date>Drive and media information from media mount: 9/4/2013 9:17:38 AM</media_mount_date>
<drive_name>Drive Name: BUMMERMMS0-B2D</drive_name>
<media_label>Media Label: B2D006436</media_label>
<media_guid>Media GUID: {0c9fa835-f3dc-42fe-a7e6-9894842919c9}</media_guid>
<media_overwrite_date>Overwrite Protected Until: 12/30/9999 4:00:00 PM</media_overwrite_date>
<media_append_date>Appendable Until: 12/30/9999 4:00:00 PM</media_append_date>
<media_set_target>Targeted Media Set Name: WEEKLY - FULL</media_set_target>
</media_drive_and_media_info>
</joblog>';

EXEC sp_xml_preparedocument @dochandle OUTPUT, @xmldocument;

SELECT
server,
name,
start_time,
type,
log_name,
media_mount_date
FROM OPENXML(@dochandle, 'joblog/header', 1)
WITH
(
server [varchar](20) 'server',
name [varchar](300) 'name',
start_time [varchar](100) 'start_time',
type varchar(20) 'type',
log_name varchar(100) 'log_name',
media_mount_date varchar(100) 'media_mount_date'
)


EXEC sp_xml_removedocument @dochandle
GO




To get both Header and Media_drive_and_media_info info I have tried CROSS APPLY without success:
SELECT
server,
name,
start_time,
type,
log_name,
media_mount_date
FROM OPENXML(@dochandle, 'joblog/header', 1)
CROSS APPLY OPENXML (@dochandle, 'joblog', 1)
WITH
(
server [varchar](20) 'server',
name [varchar](300) 'name',
start_time [varchar](100) 'start_time',
type varchar(20) 'type',
log_name varchar(100) 'log_name',
media_mount_date varchar(100) 'media_mount_date'
)

The CROSS APPLY attempt retrieved only the media_mount_date with NULLs for all preceding columns.

Can you help me rewrite the query so that I can I traverse up and down the nodes hierarchies to get ALL data extracted?

Thanks.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Here's a slightly different approach without the OPENXML approach (I, personally, find OPENXML more complicted and it seems to be less efficient in most cases):

SELECT
TempXML1.Node1.value('(server/text())[1]', 'varchar(50)') AS [server],
TempXML1.Node1.value('(name/text())[1]', 'varchar(50)') AS [name],
TempXML1.Node1.value('(start_time/text())[1]', 'varchar(50)') AS [start_time],
TempXML1.Node1.value('(type/text())[1]', 'varchar(50)') AS [type],
TempXML1.Node1.value('(log_name/text())[1]', 'varchar(50)') AS [log_name],
TempXML2.Node2.value('(text())[1]', 'varchar(50)') AS myname ,
TempXML3.Node3.value('(media_mount_date/text())[1]', 'varchar(50)') AS media_mount_date,
TempXML3.Node3.value('(drive_name/text())[1]', 'varchar(50)') AS drive_name,
TempXML3.Node3.value('(media_label/text())[1]', 'varchar(50)') AS media_label,
TempXML3.Node3.value('(media_guid/text())[1]', 'varchar(50)') AS media_guid,
TempXML3.Node3.value('(media_overwrite_date/text())[1]', 'varchar(50)') AS media_overwrite_date,
TempXML3.Node3.value('(media_append_date/text())[1]', 'varchar(50)') AS media_append_date,
TempXML3.Node3.value('(media_set_target/text())[1]', 'varchar(50)') AS media_set_target
FROM @xmldocument.nodes('/joblog') TempXML (Node)
OUTER APPLY TempXML.Node.nodes('header') AS TempXML1(Node1)
OUTER APPLY TempXML.Node.nodes('media_mount_date') AS TempXML2(Node2)
OUTER APPLY TempXML.Node.nodes('media_drive_and_media_info') AS TempXML3(Node3)





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 7426
This is what you are looking for


EXEC sp_xml_preparedocument @dochandle OUTPUT, @xmldocument;

SELECT
server,
name,
start_time,
type,
log_name,
media_mount_date,
drive_name,
media_label,
media_guid,
media_overwrite_date,
media_append_date,
media_set_target
FROM OPENXML(@dochandle, 'joblog', 1)
WITH
(
server [varchar](20) 'header/server/text()',
name [varchar](300) 'header/name/text()',
start_time [varchar](100) 'header/start_time/text()',
type varchar(20) 'header/type/text()',
log_name varchar(100) 'header/log_name/text()',
media_mount_date varchar(100) 'media_mount_date/text()',
-- some others you may be interested in...
drive_name varchar(100) 'media_drive_and_media_info/drive_name/text()',
media_label varchar(100) 'media_drive_and_media_info/media_label/text()',
media_guid varchar(100) 'media_drive_and_media_info/media_guid/text()',
media_overwrite_date varchar(100) 'media_drive_and_media_info/media_overwrite_date/text()',
media_append_date varchar(100) 'media_drive_and_media_info/media_append_date/text()',
media_set_target varchar(100) 'media_drive_and_media_info/media_set_target/text()'
)

EXEC sp_xml_removedocument @dochandle;



Note that I included the "text()" node... This is a good "best practice" for when your are dealing exclusively with atomic text values.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 7426
LutzM (10/7/2013)
Here's a slightly different approach without the OPENXML approach (I, personally, find OPENXML more complicted and it seems to be less efficient in most cases):

SELECT
TempXML1.Node1.value('(server/text())[1]', 'varchar(50)') AS [server],
TempXML1.Node1.value('(name/text())[1]', 'varchar(50)') AS [name],
TempXML1.Node1.value('(start_time/text())[1]', 'varchar(50)') AS [start_time],
TempXML1.Node1.value('(type/text())[1]', 'varchar(50)') AS [type],
TempXML1.Node1.value('(log_name/text())[1]', 'varchar(50)') AS [log_name],
TempXML2.Node2.value('(text())[1]', 'varchar(50)') AS myname ,
TempXML3.Node3.value('(media_mount_date/text())[1]', 'varchar(50)') AS media_mount_date,
TempXML3.Node3.value('(drive_name/text())[1]', 'varchar(50)') AS drive_name,
TempXML3.Node3.value('(media_label/text())[1]', 'varchar(50)') AS media_label,
TempXML3.Node3.value('(media_guid/text())[1]', 'varchar(50)') AS media_guid,
TempXML3.Node3.value('(media_overwrite_date/text())[1]', 'varchar(50)') AS media_overwrite_date,
TempXML3.Node3.value('(media_append_date/text())[1]', 'varchar(50)') AS media_append_date,
TempXML3.Node3.value('(media_set_target/text())[1]', 'varchar(50)') AS media_set_target
FROM @xmldocument.nodes('/joblog') TempXML (Node)
OUTER APPLY TempXML.Node.nodes('header') AS TempXML1(Node1)
OUTER APPLY TempXML.Node.nodes('media_mount_date') AS TempXML2(Node2)
OUTER APPLY TempXML.Node.nodes('media_drive_and_media_info') AS TempXML3(Node3)





No need for all those OUTER APPLIES, you could just do this:


SELECT
TempXML.Node.value('(header/server/text())[1]', 'varchar(50)') AS [server1],
TempXML.Node.value('(header/server/text())[1]', 'varchar(50)') AS [server],
TempXML.Node.value('(header/name/text())[1]', 'varchar(50)') AS [name],
TempXML.Node.value('(header/start_time/text())[1]', 'varchar(50)') AS [start_time],
TempXML.Node.value('(header/type/text())[1]', 'varchar(50)') AS [type],
TempXML.Node.value('(header/log_name/text())[1]', 'varchar(50)') AS [log_name],
TempXML.Node.value('(media_mount_date/text())[1]', 'varchar(50)') AS [media_mount_date],
TempXML.Node.value('(media_drive_and_media_info/drive_name/text())[1]', 'varchar(50)') AS drive_name,
TempXML.Node.value('(media_drive_and_media_info/media_label/text())[1]', 'varchar(50)') AS media_label,
TempXML.Node.value('(media_drive_and_media_info/media_guid/text())[1]', 'varchar(50)') AS media_guid,
TempXML.Node.value('(media_drive_and_media_info/media_overwrite_date/text())[1]', 'varchar(50)') AS media_overwrite_date,
TempXML.Node.value('(media_drive_and_media_info/media_append_date/text())[1]', 'varchar(50)') AS media_append_date,
TempXML.Node.value('(media_drive_and_media_info/media_set_target/text())[1]', 'varchar(50)') AS media_set_target
FROM @xmldocument.nodes('/joblog') TempXML (Node)



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
polkadot
polkadot
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
Alan and Lutz, very glad for reply. Thank you for showing me syntax that'll do the job. I will work on this tonight.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
@Alan:
as long as it is guaranteed there'll always be a single node for each and every node block referenced in the code, I agree that your code is more efficient.
But how about having an unknown number of media represented by multiple nodes of media_drive_and_media_info?

In such a scenario your code would just return the first node ignoring all following nodes, finally leading to a "wrong" result set.

To me it seems like a classic example of "it depends" ;-)
If it can be guaranteed that each node will be unique, I'd recommend your solution. Otherwise I wouldn't. It depends. Cool



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search