August 6, 2020 at 6:08 pm
I'm sure this has been asked a 1000 times before, but every example I've seen works great, but doesn't seem to translate well into my XML format - I have no control over the XML
I have the following XML:
DECLARE @xml XML = '
<Data>
<DataInfo>
<Org Code="Org Name"/>
</DataInfo>
<Details>
<Box>
<Containers>
<Segment>
<Seg Code="123456"/>
<Lines>
<Line>
<LineVal Code="ABCD" />
<LineDate>2020-01-01</LineDate>
<Quantity>1</Quantity>
</Line>
<Line>
<LineVal Code="EFGH" />
<LineDate>2020-02-01</LineDate>
<Quantity>1</Quantity>
</Line>
</Lines>
</Segment>
<Segment>
<Seg Code="098765"/>
<Lines>
<Line>
<LineVal Code="QWERT" />
<LineDate>2020-03-01</LineDate>
<Quantity>3</Quantity>
</Line>
<Line>
<LineVal Code="ASDFG" />
<LineDate>2020-03-12</LineDate>
<Quantity>2</Quantity>
</Line>
</Lines>
</Segment>
</Containers>
</Box>
</Details>
</Data>
'
And ideally, what I'd end up with is:
segCode | lineCode | lineDate | lineQuantity
123456|ABCD|2020-01-01 | 1
123456|EFGH|2020-02-01|1
098765|QWERT|2020-03-01|3
098765|ASDFG|2020-03-12|2
What I'm actually getting though is
segCode | lineCode | lineDate
098765 | ABCD | NULL | NULL
098765 | ASDFG | NULL | NULL
098765 | EFGH | NULL | NULL
098765 | QWERT | NULL | NULL
123456 | ABCD | NULL | NULL
123456 | EFGH | NULL | NULL
123456 | ABCD | NULL | NULL
123456 | EFGH | NULL | NULL
Using this code, which honestly is a little cobbled together from other examples I'm seeing. I'm not sure how to get LineDate and Quantity.
SELECT DISTINCT
segRef.value('@Code', 'VARCHAR(10)') AS segCode,
lineValRef.value('@Code', 'VARCHAR(10)') AS lineCode,
lineRef.value('@LineDate', 'VARCHAR(20)') AS lineDate,
lineRef.value('@Quantity', 'INT') AS lineQuantity
FROM
@xml.nodes('/Data') AS d(dataRef) CROSS APPLY
dataRef.nodes('//Details/Box/Containers/Segment/Seg') AS s(segRef) CROSS APPLY
segRef.nodes('//Lines/Line') AS l(lineRef) CROSS APPLY
lineRef.nodes('//LineVal') AS lv(lineValRef)
Can someone point me in the right direction here?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
August 6, 2020 at 8:21 pm
I have this solved in case anyone is wondering how to get to the result I was looking for:
SELECT
X.Y.value('(Seg/@Code)[1]', 'VARCHAR(50)') AS segCode
,Q.W.value('(LineVal/@Code)[1]', 'VARCHAR(50)') AS lineCode
,Q.W.value('(LineDate/.)[1]', 'DATE') AS lineDate
,Q.W.value('(Quantity/.)[1]', 'INT') AS lineQuantity
FROM
@xml.nodes('/Data/Details/Box/Containers/Segment') AS X(Y)
CROSS APPLY X.Y.nodes('Lines/Line') AS Q(W)
Gives me the expected:
segCode | lineCode | lineDate | lineQuantity
123456 | ABCD | 2020-01-01 | 1
123456 | EFGH | 2020-02-01 | 1
098765 | QWERT | 2020-03-01 | 3
098765 | ASDFG | 2020-03-12 | 2
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply