Shredding with XML schema and XML data in one XML column

Question

Post reply

Shredding with XML schema and XML data in one XML column

Eschergoedel

Old Hand

Points: 348
More actions
March 4, 2016 at 8:27 am

#325343

Hi:
thank you for your time and advice in advance.
I inherited a SQL Server 2012 DB with a table with an XML column that contains first the schema and then the xml instance data. The schema and instance data may change and I have no control over the schema and the instance.
What is the best practice approach for shredding the XML with such an XML column. How can I dynamically define the schema for the instance?
Thank you for your help,
EG

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 1

Perhaps you can share some DDL and data to understand what you mean.

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

This link should help get you started:

https://msdn.microsoft.com/en-us/library/ms188282.aspx

If you post some ddl and sample data we can demonstrate on that.

"I cant 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."

-- Itzik Ben-Gan 2001

Eschergoedel Old Hand Points: 348 More actions · Answer 3

Hi:

thank you for responding.

I am not sure I can share the DDL and XML for confidentiality reasons -that is an issue I know;

I am asking if I have to and can create a schema dynamically from the schema loaded in the XML column and use it to parse the XML?

Currently, I am trying to just use namespaces las indicated below. But the values returned are null however I vary the syntax. I am sure the XPATH is good and I am sure the XML column has valid schema and instance data. It would help me to simply understand if the nodes () method can jump to the instance data and ignore the schema part of the XML column? I have been successful in shredding XML with similar approaches as below; its just really confusing me to have the schema at the beginning of the column and I could not find any directions anywhere as to how to deal with this.

USE [XMLDB]

GO

DECLARE @DData as XML, @pathHandle as INT

SELECT @DData = XMLData FROM [XMLDB].[dbo].[XMLTable]

EXEC sp_xml_preparedocument @pathHandle OUTPUT, @DData

;WITH XMLNAMESPACES('http://www.<omitted for security reasons>' AS MD,

DEFAULT 'http://www.<omitted for security reasons>')

SELECT

GroupData.value('@AccountNumber','VARCHAR(50)') AS AccountNumber

,GroupData.value('@AccountType', 'VARCHAR(50)') AS AccountType

FROM [XMLDB].[dbo].[XMLTable]

CROSS APPLY @DData.nodes('MD:Document') AS XD(Document)

CROSS APPLY document.nodes('PortfolioOrGroup') AS XD2(PortfolioOrGroup)

CROSS APPLY PortfolioOrGroup.nodes('Holdings') AS XD3(Holdings)

CROSS APPLY Holdings.nodes('GroupData') AS XD4(GroupData)

EXEC sp_xml_removedocument @pathHandle