May 3, 2016 at 3:45 am
Hi All,
I've been searching the web for an answer, but I can't seem to find an answer for the question at hand. Basically, I've got a hierarchical XML string, containing a unique "entity" number followed by a 'dynamic' number of <field> elements. (By dynamic, I mean I won't know the list beforehand).
An excerpt is as follows:
<?xml version="1.0"?>
<data>
<entity id="ABCDEFG1234">
<fields>
<field code="990001" value=""></field>
<field code="ACH0007" value="Unknown"></field>
<field code="ACH0008" value=""></field>
<field code="ACH0009" value=" "></field>
<field code="AFA0002" value="0"></field>
<field code="AFA0003" value="GBP"></field>
<field code="AHF0004" value="0.00"></field>
<field code="AHP0001" value="N"></field>
<field code="AHP0006" value=",,"></field>
<field code="RFI0001" value="TEST_COMPANY"></field>
<field code="RFI0002" value="abcd.efgh.ijkl"></field>
<field code="RFI0003" value="23842384234"></field>
<field code="RFI0004" value="Private Company Co"></field>
<field code="RFI0017" value="999"></field>
<field code="RFI0021" value="GB"></field>
<field code="RFP0001" value="Private Company Ltd"></field>
<field code="RFP0002" value="Address Line 1"></field>
<field code="RFP0003" value="Address Line 2"></field>
<field code="RFP0004" value="Address Line 3"></field>
<field code="RFP0005" value="PostCode"></field>
<field code="RFP0006" value="Country"></field>
<field code="RFP0007" value="N"></field>
<field code="RFY0001" value="N"></field>
<field code="RPP0001" value="01/04/2016"></field>
<field code="SBE0001" value="TEST_CO"></field>
<field code="SBE0002" value="Test Co"></field>
<field code="SBE0003" value="Test Company Ltd"></field>
<field code="SBE0005" value="2016/0603/10212"></field>
<field code="SBE0006" value="2348239082"></field>
<field code="SBE0007" value="abcd.efgh.ijkl"></field>
<field code="SBE0008" value="Referral Company"></field>
<field code="SBE0009" value="Ref. Co Address Line 1"></field>
<field code="SBE0010" value="Ref. Co Address Line 2"></field>
<field code="SBE0011" value="Ref. Co Address Line 3"></field>
<field code="SBE0012" value="Ref. Co Post Code"></field>
<field code="SPN0011" value="0"></field>
<field code="SSY0001" value="MySys"></field>
<field code="SSY0002" value="1.0"></field>
<field code="SSY0003" value=" "></field>
</fields>
</entity>
<entity id="HIJKLMNOP5678">
<fields>
<field code="990001" value=""></field>
<field code="ACH0007" value="Unknown"></field>
<field code="ACH0008" value=""></field>
<field code="ACH0009" value=" "></field>
<field code="AFA0002" value="0"></field>
<field code="AFA0003" value="USD"></field>
<field code="AHF0004" value="0.00"></field>
<field code="AHP0001" value="N"></field>
<field code="AHP0006" value=",,"></field>
<field code="RFI0001" value="KP_COMPANY"></field>
<field code="RFI0002" value="mnop.qrst.uvwx"></field>
<field code="RFI0003" value="456456546456"></field>
<field code="RFI0004" value="My Test Company Co"></field>
<field code="RFI0017" value="999"></field>
<field code="RFI0021" value="US"></field>
<field code="RFP0001" value="My Test Company Ltd"></field>
<field code="RFP0002" value="Address Line 1"></field>
<field code="RFP0003" value="Address Line 2"></field>
<field code="RFP0004" value="Address Line 3"></field>
<field code="RFP0005" value="PostCode"></field>
<field code="RFP0006" value="Country"></field>
<field code="RFP0007" value="N"></field>
<field code="RFY0001" value="N"></field>
<field code="RPP0001" value="01/04/2016"></field>
<field code="SBE0001" value="TEST_CO"></field>
<field code="SBE0002" value="Test Co"></field>
<field code="SBE0003" value="Test Company Ltd"></field>
<field code="SBE0005" value="2015/2342/97890"></field>
<field code="SBE0006" value="345345435345"></field>
<field code="SBE0007" value="abcd.efgh.ijkl"></field>
<field code="SBE0008" value="Referral Company"></field>
<field code="SBE0009" value="Ref. Co Address Line 1"></field>
<field code="SBE0010" value="Ref. Co Address Line 2"></field>
<field code="SBE0011" value="Ref. Co Address Line 3"></field>
<field code="SBE0012" value="Ref. Co Post Code"></field>
<field code="SPN0011" value="0"></field>
<field code="SSY0001" value="MySys"></field>
<field code="SSY0002" value="1.0"></field>
<field code="SSY0003" value=" "></field>
</fields>
</entity>
</data>
All rows will have the same number of fields in them though.
What I'd like to do is come up with a query that converts the XML into rows like as follows:
EntityID "990001" "ACH0007" "ACH0008" ......
------------------------------------------------------------
"ABCDEFG1234" "" "Unknown" "" .......
"HIJKLMNOP5678" "" "Unknown" "" .......
I've been using OPENXML and XPATH to query these, and so far have been able to read the data but I'm getting two rows that contain the same data. I can see where the problem lies, but due to my lack of experience with XML I'm unsure as to how to fix it.
DECLARE @XMLXML = '<xml as above>'
DECLARE @SQL nvarchar(max) =
'SELECT '
+(
SELECT ',''' + T.FieldValue+ ''' as '+quotename(T.FieldCode)
FROM (
SELECTT.X.value('@code', 'nvarchar(128)') as FieldCode
,T.X.value('@value', 'nvarchar(128)') as FieldValue
FROM@XML.nodes('/data/entity[1]/fields/field') as T(X)
) AS T
FOR XML PATH(''), TYPE
).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+
' FROM @XML.nodes(''/data/entity'') as T(X)';
SELECT @SQL
exec sp_executesql @SQL, N'@XML xml', @XML;
Thanks in advance for your help!
Kevin.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
May 3, 2016 at 6:20 am
Quick suggestion, add DISTINCT to the select otherwise the two data/entity nodes will always produce duplicate output
😎
DECLARE @XMLXML = '<xml as above>'
DECLARE @SQL nvarchar(max) =
'SELECT DISTINCT '
+(
SELECT ',''' + T.FieldValue+ ''' as '+quotename(T.FieldCode)
FROM (
SELECTT.X.value('@code', 'nvarchar(128)') as FieldCode
,T.X.value('@value', 'nvarchar(128)') as FieldValue
FROM@XML.nodes('/data/entity[1]/fields/field') as T(X)
) AS T
FOR XML PATH(''), TYPE
).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+
' FROM @XML.nodes(''/data/entity'') as T(X)';
SELECT @SQL
exec sp_executesql @SQL, N'@XML xml', @XML;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply