May 7, 2012 at 8:01 am
Hi all,
I have a problem with a nested xml. It's triple nested, and the innermost part of it is null in some cases. I created a simplified version of that xml, as you can see below. I'd like to kniow how to modify my select statement so it shows all elements of that xml, and the null fields are to be null.
What I get is this:
RecID field1 RecID2 field2 RecID3 field3 field4 field5
1 2 1 2 1 2 2 TOS
but I want three rows.
Here's my xml and my select statement...
DECLARE @xml XML
SET @xml = '
<OuterRoot>
<Outer>
<RecID>1</RecID>
<field1>2</field1>
<InnerRoot>
<Inner>
<RecID2>1</RecID2>
<field2>2</field2>
<InnermostRoot>
<Innermost>
<RecID3>1</RecID3>
<field3>2</field3>
<field4>2</field4>
<field5>TOS</field5>
</Innermost>
</InnermostRoot>
</Inner>
</InnerRoot>
</Outer>
<Outer>
<RecID>2</RecID>
<field1>3</field1>
<InnerRoot>
<Inner>
<RecID2>2</RecID2>
<field2>3</field2>
</Inner>
</InnerRoot>
</Outer>
<Outer>
<RecID>3</RecID>
<field1>2</field1>
<InnerRoot>
<Inner>
<RecID2>3</RecID2>
<field2>2</field2>
</Inner>
</InnerRoot>
</Outer>
</OuterRoot>'
SELECT
Outers.Element.value('(RecID)[1]', 'INT') AS RecID,
Outers.Element.value('(field1)[1]', 'INT') AS field1,
Inners.Element.value('(RecID2)[1]', 'INT') AS RecID2,
Inners.Element.value('(field2)[1]', 'INT') AS field2,
Innermost.Element.value('(RecID3)[1]', 'INT') AS RecID3,
Innermost.Element.value('(field3)[1]', 'INT') AS field3,
Innermost.Element.value('(field4)[1]', 'INT') AS field4,
Innermost.Element.value('(field5)[1]', 'VARCHAR(3)') AS field5
FROM
@xml.nodes('/OuterRoot/Outer') AS Outers(Element)
CROSS APPLY
Outers.Element.nodes('InnerRoot/Inner') AS Inners(Element)
CROSS APPLY
Inners.Element.nodes('InnermostRoot/Innermost') AS Innermost(Element)
I appreciate some help.
May 7, 2012 at 8:37 am
Can you show me what your 3 rows should look like?
_________________________________
seth delconte
http://sqlkeys.com
May 7, 2012 at 8:54 am
Sure. Here's simplified (please ignore lack of PK FK etc) set up to create the xml...
DECLARE @tmp1 TABLE (
RecID INT,
field1 INT)
DECLARE @tmp2 TABLE (
RecID2 INT,
field2 INT)
DECLARE @tmp3 TABLE (
RecID3 INT,
field3 INT,
field4 INT,
field5 VARCHAR(3))
INSERT INTO @tmp1
VALUES (1, 2), (2, 3), (3, 2)
INSERT INTO @tmp2
VALUES (1, 2), (2, 3), (3, 2)
INSERT INTO @tmp3
VALUES (1, 2, 2, 'TOS'), (4, 5, 3, 'TWQ'), (6, 7, 3, 'KSD')
SELECT
W.RecID,
W.field1, (
SELECT
U.RecID2,
U.field2, (
SELECT
P.RecID3,
P.field3,
P.field4,
P.field5
FROM
@tmp3 P
WHERE
P.RecID3 = U.RecID2
FOR XML PATH ('Innermost'), ROOT('InnermostRoot'), TYPE )
FROM
@tmp2 U
WHERE U.RecID2 = W.RecID
FOR XML PATH ('Inner'), ROOT('InnerRoot'), TYPE )
FROM
@tmp1 W
FOR XML PATH ('Outer'), ROOT('OuterRoot'), TYPE
The result is a product of left join of all three tables (without xml, just select) ... Maybe I'm forming the xml wrong?
RecID field1 RecID2 field2 RecID3 field3 field4 field5
1 2 1 2 1 2 2 TOS
2 3 2 3 NULL NULL NULL NULL
3 2 3 2 NULL NULL NULL NULL
May 7, 2012 at 12:39 pm
If you are not opposed to hard-coding in empty nodes in an ISNULL, this works:
SELECT
W.RecID,
W.field1, (
SELECT
U.RecID2,
U.field2,
isnull((
SELECT
P.RecID3,
P.field3,
P.field4,
P.field5
FROM
@tmp3 P
WHERE
P.RecID3 = U.RecID2
FOR XML PATH ('Innermost'), ROOT('InnermostRoot'),TYPE ),
'<InnermostRoot><Innermost>
<RecID3> </RecID3>
<field3> </field3>
<field4> </field4>
<field5> </field5>
</Innermost></InnermostRoot>')
FROM
@tmp2 U
WHERE U.RecID2 = W.RecID
FOR XML PATH ('Inner'), ROOT('InnerRoot'),TYPE )
FROM
@tmp1 W
FOR XML PATH ('Outer'), ROOT('OuterRoot'), TYPE
It outputs:
RecID field1 RecID2 field2 RecID3 field3 field4 field5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ------
1 2 1 2 1 2 2 TOS
2 3 2 3 0 0 0
3 2 3 2 0 0 0
(3 row(s) affected)
_________________________________
seth delconte
http://sqlkeys.com
May 7, 2012 at 2:01 pm
Well, probably not a problem if this is last resort. Or I assume I might change some table structure to deal with this.
Thank you so much, greatly appreciated 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply