Help with a "simple" XML example, please

Question

Post reply

Help with a "simple" XML example, please

Jeff Moden

SSC Guru

Points: 1004441
More actions
November 8, 2012 at 3:15 pm

#264608

Don't laugh too hard but I'm definitely out of my element and need some help in the form of an example, please.
Here's a sample of some XML that I'm going to have to work with (shred on a regular basis).
--===== Create the test table
CREATE TABLE #MyHead
(
SomeID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SomeXML XML
)
;
--===== Populate the table with a row that all rows will resemble.
-- There can be any number of DueDates including none.
INSERT INTO #MyHead(SomeXML)
VALUES (
'<RootA>
<Payees>
<Payee>
<EDICompanyQualifier>XX</EDICompanyQualifier>
<EDICompanyCode>123456789</EDICompanyCode>
</Payee>
</Payees>
<DueDates>
<DueDate>2012-09-01</DueDate>
<DueDate>2012-09-15</DueDate>
<DueDate>2012-10-01</DueDate>
<DueDate>2012-10-15</DueDate>
<DueDate>2012-11-01</DueDate>
<DueDate>2012-11-15</DueDate>
<DueDate>2012-12-01</DueDate>
<DueDate>2012-12-15</DueDate>
</DueDates>
</RootA>'
)
;
I need to flatten that out so that it looks like the result of the following code.
--===== This generates what I'd like the result to look like
SELECT EDICompanyQualifier = CAST('XX' AS CHAR(2)),
EDICompanyCode = CAST('123456789' AS INT),
DueDate = CAST(d.DueDate AS DATETIME)
FROM (
SELECT '2012-09-01' UNION ALL
SELECT '2012-09-15' UNION ALL
SELECT '2012-10-01' UNION ALL
SELECT '2012-10-15' UNION ALL
SELECT '2012-11-01' UNION ALL
SELECT '2012-11-15' UNION ALL
SELECT '2012-12-01' UNION ALL
SELECT '2012-12-15'
) d (DueDate)
ORDER BY DueDate
;
Results:
EDICompanyQualifier EDICompanyCode DueDate
------------------- -------------- -----------------------
XX 123456789 2012-09-01 00:00:00.000
XX 123456789 2012-09-15 00:00:00.000
XX 123456789 2012-10-01 00:00:00.000
XX 123456789 2012-10-15 00:00:00.000
XX 123456789 2012-11-01 00:00:00.000
XX 123456789 2012-11-15 00:00:00.000
XX 123456789 2012-12-01 00:00:00.000
XX 123456789 2012-12-15 00:00:00.000
(8 row(s) affected)
What's the easiest way to shred (using XML techniques) the XML to give the result above? A good example would teach me a lot because, although simple looking, it's complex to serve as a really good example that I can expand upon later.
Thanks for the leg up, folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Does this help ?

--===== This shreds the XML by extracting each "DueDate" node (SomeXml.nodes)

--===== Then pullng the data from the Payee Node which is found by navigating

--===== to the parent (..) of the parent(..) of the DueDate (i.e. RootA)

SELECT

EDICompanyQualifier = nd.value('(../../Payees/Payee/EDICompanyQualifier/text())[1]','CHAR(2)')

, EDICompanyCode = nd.value('(../../Payees/Payee/EDICompanyCode/text())[1]','INT')

, DueDate = nd.value('(./text())[1]','DATETIME')

FROM #MyHead

-- The CROSS APPLY ... nodes clause generates one row per RootA/DueDates/DueDate in the XML

CROSS APPLY SomeXml.nodes('RootA/DueDates/DueDate') x(nd)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

An alternative, which will allow you to more easily see how you're connecting up the different node connections, is as follows.

I've left a note in the code, but the big deal about using it this way is the '.' component that trails. What that does is it pulls data for each repition of a tag set, so that it doesn't consider the first element the only element.

This tends to work better with attribute based XML instead of element XML, but it functions.

Of course, the final bit on JOIN 1=1? That's because OPENXML does not behave well with FULL OUTER JOIN. I have no blinkin' clue why and I spent an hour doing a headscratch about it before I finally gave up and gamed the system.

DECLARE @xml XML,

@handle INT

SET @xml = '<RootA>

<Payees>

<Payee>

<EDICompanyQualifier>XX</EDICompanyQualifier>

<EDICompanyCode>123456789</EDICompanyCode>

</Payee>

<Payee>

<EDICompanyQualifier>YY</EDICompanyQualifier>

<EDICompanyCode>987654321</EDICompanyCode>

</Payee>

</Payees>

<DueDates>

<DueDate>2012-09-01</DueDate>

<DueDate>2012-09-15</DueDate>

<DueDate>2012-10-01</DueDate>

<DueDate>2012-10-15</DueDate>

<DueDate>2012-11-01</DueDate>

<DueDate>2012-11-15</DueDate>

<DueDate>2012-12-01</DueDate>

<DueDate>2012-12-15</DueDate>

</DueDates>

</RootA>'

EXEC dbo.sp_xml_preparedocument @handle OUTPUT, @xml

-- The reason the '.' is required is to allow multi-row pickup of the inner element,

-- else it only looks for the single element. If you remove the '.' you'll find it

-- will only pickup the first row in the element listing.

SELECT *

FROM

OPENXML( @handle, 'RootA/Payees/Payee/.', 3)

WITH ( EDICompanyQualifier CHAR(2) ,

EDICompanyCode INT ) AS Payees

JOIN

OPENXML ( @handle, '/RootA/DueDates/DueDate', 2)

WITH (DueDate DATETIME '.') AS DueDates

ON 1=1

EXEC sp_xml_removeDocument @handle

EDIT: Sorry, left some test code in there.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Thank you both. I don't feel so bad not being able to figure it on my own. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

mister.magoo (11/8/2012)
Does this help ?
--===== This shreds the XML by extracting each "DueDate" node (SomeXml.nodes)
--===== Then pullng the data from the Payee Node which is found by navigating
--===== to the parent (..) of the parent(..) of the DueDate (i.e. RootA)
SELECT
EDICompanyQualifier = nd.value('(../../Payees/Payee/EDICompanyQualifier/text())[1]','CHAR(2)')
, EDICompanyCode = nd.value('(../../Payees/Payee/EDICompanyCode/text())[1]','INT')
, DueDate = nd.value('(./text())[1]','DATETIME')
FROM #MyHead
-- The CROSS APPLY ... nodes clause generates one row per RootA/DueDates/DueDate in the XML
CROSS APPLY SomeXml.nodes('RootA/DueDates/DueDate') x(nd)

That does, in fact, help. I realize that I didn't have it in my test data and so apologize for that but if you add an extra Payee, only the first Payee shows up.

It's a little spooky that XML does that. We could miss a lot of data that way if such a thing were to happen.

I do very much appreciate the help, though. Thanks, Magoo.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

Evil Kraig F (11/8/2012)
An alternative, which will allow you to more easily see how you're connecting up the different node connections, is as follows.
I've left a note in the code, but the big deal about using it this way is the '.' component that trails. What that does is it pulls data for each repition of a tag set, so that it doesn't consider the first element the only element.
This tends to work better with attribute based XML instead of element XML, but it functions.
Of course, the final bit on JOIN 1=1? That's because OPENXML does not behave well with FULL OUTER JOIN. I have no blinkin' clue why and I spent an hour doing a headscratch about it before I finally gave up and gamed the system.
DECLARE @xml XML,
@handle INT
SET @xml = '<RootA>
<Payees>
<Payee>
<EDICompanyQualifier>XX</EDICompanyQualifier>
<EDICompanyCode>123456789</EDICompanyCode>
</Payee>
<Payee>
<EDICompanyQualifier>YY</EDICompanyQualifier>
<EDICompanyCode>987654321</EDICompanyCode>
</Payee>
</Payees>
<DueDates>
<DueDate>2012-09-01</DueDate>
<DueDate>2012-09-15</DueDate>
<DueDate>2012-10-01</DueDate>
<DueDate>2012-10-15</DueDate>
<DueDate>2012-11-01</DueDate>
<DueDate>2012-11-15</DueDate>
<DueDate>2012-12-01</DueDate>
<DueDate>2012-12-15</DueDate>
</DueDates>
</RootA>'
EXEC dbo.sp_xml_preparedocument @handle OUTPUT, @xml
-- The reason the '.' is required is to allow multi-row pickup of the inner element,
-- else it only looks for the single element. If you remove the '.' you'll find it
-- will only pickup the first row in the element listing.
SELECT *
FROM
OPENXML( @handle, 'RootA/Payees/Payee/.', 3)
WITH ( EDICompanyQualifier CHAR(2) ,
EDICompanyCode INT ) AS Payees
JOIN
OPENXML ( @handle, '/RootA/DueDates/DueDate', 2)
WITH (DueDate DATETIME '.') AS DueDates
ON 1=1
EXEC sp_xml_removeDocument @handle
EDIT: Sorry, left some test code in there.

Thanks for the help.

I looked up what the 3rd operand stands for in OPENXML and it appears to control whether you're using element or attribute based XML. In 2k5 BOL, the supposedly allowed values are 0, 1, 2, and 8. What does "3" mean?

Also, how do you know when to add the dots or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Jeff Moden (11/9/2012)
Does this help ?
That does, in fact, help. I realize that I didn't have it in my test data and so apologize for that but if you add an extra Payee, only the first Payee shows up.

I did notice that you only had one "Payee" node, but thought that you would likely only have one "Payee" per set of due dates - after all, it doesn't really make much sense to have one set of due dates relating to multiple payees - or does it?

A set-based analogy for the way my query is written would be that you are SELECTing from a DueDates table, with a subquery to SELECT the TOP 1 Payee from the Payees table, with an implicit JOIN based on all nodes being children of the RootA node.

Of course, if the XML is not structured to suit that query, you need another approach.

If you have some more XML that reflects what you are actually trying, I would be very happy to help 🙂

It's a little spooky that XML does that. We could miss a lot of data that way if such a thing were to happen.

There is nothing inherent in xml/XQuery that prevents the wrong query being written, just as in dml/t-sql or any other language. That is what a properly defined specification and testing are for 😀

I do very much appreciate the help, though. Thanks, Magoo.

You are most welcome - if anyone on here deserves help whenever they ask, it is you.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

A couple of points to bear in mind about OPENXML:

1. Memory ( http://msdn.microsoft.com/en-us/library/ms187367(v=sql.90).aspx

A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

2. Azure : OPENXML is not supported, so if there is any chance of moving to SQL Azure, don't start using it now.

http://msdn.microsoft.com/en-us/library/windowsazure/ee336248.aspx

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Here is a version that would handle things for multiple RootA nodes and multiple Payees, assuming you just want a cross-join of payees with due dates for now....

--===== This shreds the XML by extracting ..

--===== Each Payee Node and each DueDate , then cross joining the two sets

SELECT

EDICompanyQualifier = payee.value('(EDICompanyQualifier/text())[1]','CHAR(2)')

, EDICompanyCode = payee.value('(EDICompanyCode/text())[1]','INT')

, DueDate = duedate.value('(./text())[1]','DATETIME')

FROM

#MyHead

-- This CROSS APPLY SomeXml.nodes clause generates one row per RootA in the XML

CROSS APPLY

SomeXml.nodes('RootA') [root]([node])

-- This CROSS APPLY generates one row per Payees/Payee in the current RootA node

CROSS APPLY

[root].[node].nodes('Payees/Payee') [payees]([payee])

-- This CROSS APPLY generates one row per DueDates/DueDate in the current RootA node

CROSS APPLY

[root].[node].nodes('DueDates/DueDate') [duedates]([duedate])

Also, I never mentioned about the way Values are extracted.

You use XPath expressions (see W3Schools or BeyondRelational for some good help with those) to select the nodes you want from the XML.

To extract the value from the XML into T-SQL columns, you need to tell it what you want and how to CAST it, so you supply an XPATH query to find the Value, and a datatype for it to CAST the value into.

e.g.

payee.value('(EDICompanyQualifier/text())[1]','CHAR(2)')

In this, we are saying get me the inner text (/text()) of the First([1]) EDICompanyQualifier element from the current "payee" node and cast it as a CHAR(2).

The use of "/text()" is a performance thing - you get the same result by doing this, but slightly slower:

payee.value('(EDICompanyQualifier)[1]','CHAR(2)')

See Brad's post about splitting strings using XML for some limited testing of this performance tweak http://bradsruminations.blogspot.co.uk/2010/01/delimited-string-tennis-again-final.html

When you have mastered this part, you will probably bump up against namespaces...but that's for later :hehe:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Jeff Moden (11/9/2012)
Thanks for the help.
I looked up what the 3rd operand stands for in OPENXML and it appears to control whether you're using element or attribute based XML. In 2k5 BOL, the supposedly allowed values are 0, 1, 2, and 8. What does "3" mean?
Also, how do you know when to add the dots or not?

AARGH. Typo from stolen code that stayed behind. The '2' is correct for element centric mapping. I have absolutely no idea why the original had a 3, nor does the original post explain it. It's a useless attribute. I thought I'd edited that out too. Apologies for the confusion, they're both supposed to be 2's.

The reason you need the '.' is whenever you have the same element repeated within a node and expect multiple rows from it. Basically, without the '.', the repeated elements are treated as an error, basically the same column name twice. With the '.', you're telling the XML parser that these are multiple rows of the same element name. Normally, you'd be one node up with the DueDate being an attribute (or series of attributes/elements) belonging to the client(s), and have it as a specific element.

All the mapping does is inform the parser that this isn't an error, they're supposed to repeat.

I also would like to second Mr. Magoo above. If anyone deserves a bit of help on an issue around here, you're on the short list.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Given XML sample is a bit strange.

Having <Payees> parent node kind of suggest that it may contain multiple <Payee> nodes, but <DueDates> are outside of <Payee>. So it does look like "cross join" : set of Due Dates is relevant for the set of Payees. Is it right?

Or you will have only one single Payee in Payees (the parent node may be just added for convenience of client/server coding).

Also, you have presented single XML value in a table. Does it mean that you need to process multiple records at time? Or you need to process one single XML value (eg. input parameter in stored proc)

Why is it important?

Because when you work with XML in T-SQL it is better to stick with minimal required (optimal) processing. Creating "generic" solution may significantly effect performance.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 11

mister.magoo (11/9/2012)
Jeff Moden (11/9/2012)
Does this help ?
That does, in fact, help. I realize that I didn't have it in my test data and so apologize for that but if you add an extra Payee, only the first Payee shows up.
I did notice that you only had one "Payee" node, but thought that you would likely only have one "Payee" per set of due dates - after all, it doesn't really make much sense to have one set of due dates relating to multiple payees - or does it?

My boss gave me the original XML.

First, I wouldn't have done such things as use hardcoded dates in what boils down to a string literal, which will require future maintenance. It's like hard-coding dates into a stored proc, so far as I'm concerned.

I hadn't really thought about the possibility of having two payees on this until I saw Craig's modified sample. Since these are for scheduled insurance payments and it is possible to have more than one owner for a given property, I supposed that a future requirement could be payments split between payees. Obviously, I'll need to add a "PercentOfPayment" element to the XML itself.

A set-based analogy for the way my query is written would be that you are SELECTing from a DueDates table, with a subquery to SELECT the TOP 1 Payee from the Payees table, with an implicit JOIN based on all nodes being children of the RootA node.
Of course, if the XML is not structured to suit that query, you need another approach.
If you have some more XML that reflects what you are actually trying, I would be very happy to help 🙂

Except for the contents of the elements, the name of the actual root node name, and the possible multiple payees, the XML I posted is exactly the same as what my boss coughed up. I figured that I was making things ways too complicated to resolve the required cross-join between the Payees and the DueDates but I guess not.

It's a little spooky that XML does that. We could miss a lot of data that way if such a thing were to happen.
There is nothing inherent in xml/XQuery that prevents the wrong query being written, just as in dml/t-sql or any other language. That is what a properly defined specification and testing are for 😀

Heh... Roger THAT! I don't actually like the idea of XML (hierarchical de-normalized data in a single column in an RDBMS) and with all the good stuff that I'd heard about it, I'm still fighting the idea that I have to learn a new language to use it. Then I find out that there are element AND attribute types of nodes and this ol' brain has gone into a serious "hell no" mode. For some reason, I thought it'd be easier.

I do very much appreciate the help, though. Thanks, Magoo.
You are most welcome - if anyone on here deserves help whenever they ask, it is you.

I'm truly humbled. :blush: Thank you for your kindness and for all the great posts you've written to help people over the years. You're definitely one of the greats on this forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 12

mister.magoo (11/9/2012)
A couple of points to bear in mind about OPENXML:
1. Memory ( http://msdn.microsoft.com/en-us/library/ms187367(v=sql.90).aspx
A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.
2. Azure : OPENXML is not supported, so if there is any chance of moving to SQL Azure, don't start using it now.
http://msdn.microsoft.com/en-us/library/windowsazure/ee336248.aspx

I remembered about the memory thing. I was amazed when I first found that out. I thought "... and they say Cursors are resource hogs." I can really understand why OPENXML isn't supported on Azure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 13

mister.magoo (11/9/2012)
Here is a version that would handle things for multiple RootA nodes and multiple Payees, assuming you just want a cross-join of payees with due dates for now....
--===== This shreds the XML by extracting ..
--===== Each Payee Node and each DueDate , then cross joining the two sets
SELECT
EDICompanyQualifier = payee.value('(EDICompanyQualifier/text())[1]','CHAR(2)')
, EDICompanyCode = payee.value('(EDICompanyCode/text())[1]','INT')
, DueDate = duedate.value('(./text())[1]','DATETIME')
FROM
#MyHead
-- This CROSS APPLY SomeXml.nodes clause generates one row per RootA in the XML
CROSS APPLY
SomeXml.nodes('RootA') [root]([node])
-- This CROSS APPLY generates one row per Payees/Payee in the current RootA node
CROSS APPLY
[root].[node].nodes('Payees/Payee') [payees]([payee])
-- This CROSS APPLY generates one row per DueDates/DueDate in the current RootA node
CROSS APPLY
[root].[node].nodes('DueDates/DueDate') [duedates]([duedate])

Dang. Maybe I know more than I thought, Magoo. That's pretty much what I came up with on my own. I took it one level deeper to simplify the content of the SELECT list. Now you understand why I was asking if there were a simpler way to flatten the example I gave out. (The aliases I used are actually materialized hierarchical paths just to aid in understanding for myself).

DECLARE @XML XML;

SELECT @XML = SomeXML FROM #MyHead WHERE SomeID = 1;

SELECT EDICompanyQualifier = x3b4a.EDICompanyQualifier.value ('(text())[1]', 'CHAR(2)'),

EDICompanyCode = x3b4b.EDICompanyCode.value ('(text())[1]', 'INT'),

DueDate = x3a.DueDate.value ('(text())[1]', 'DATETIME')

FROM @XML.nodes ('RootA') x1 ([RootA])

OUTER APPLY RootA.nodes ('DueDates/DueDate') x3a (DueDate)

OUTER APPLY RootA.nodes ('Payees/Payee') x3b (Payee)

OUTER APPLY Payee.nodes ('EDICompanyQualifier') x3b4a (EDICompanyQualifier)

OUTER APPLY Payee.nodes ('EDICompanyCode') x3b4b (EDICompanyCode)

;

Also, I never mentioned about the way Values are extracted.
You use XPath expressions (see W3Schools or BeyondRelational for some good help with those) to select the nodes you want from the XML.
To extract the value from the XML into T-SQL columns, you need to tell it what you want and how to CAST it, so you supply an XPATH query to find the Value, and a datatype for it to CAST the value into.
e.g.
payee.value('(EDICompanyQualifier/text())[1]','CHAR(2)')
In this, we are saying get me the inner text (/text()) of the First([1]) EDICompanyQualifier element from the current "payee" node and cast it as a CHAR(2).
The use of "/text()" is a performance thing - you get the same result by doing this, but slightly slower:
payee.value('(EDICompanyQualifier)[1]','CHAR(2)')
See Brad's post about splitting strings using XML for some limited testing of this performance tweak http://bradsruminations.blogspot.co.uk/2010/01/delimited-string-tennis-again-final.html
When you have mastered this part, you will probably bump up against namespaces...but that's for later :hehe:

Excellent Tips, good Sir. Thank you again for taking the time to help me understand this. Your pointers have been most valuable in me understanding this stuff rather than just doing CPR (Cut, Paste, Replace), which I've never cared to do. Since I have to maintain code, I like to understand what I'm doing. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Ha!, you were just pretending not to know all along! 😀

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);