SQL - XML Parsing from a non-standard XML format

Question

We have a third party app which generates an XML file with data which is submitted from a mobile device; this comes back into a SQL Database Table and sits inside an XML type field.

I need to pull out specific answers which I seem to have figured out to a certain extend but I would like to know if I could make this work with the 'nodes' or some form of Outer Apply/Cross Apply Join to then query direct rather than a full line per value.

Examples below:

CREATE TABLE [dbo].[zz_PhoneData_Test](
    [RecID] [bigint] NOT NULL,
    [TasksetID] [bigint] NULL,
    [FormData] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [reports].[zz_PhoneData_Test] ([RecID],[TasksetID], [FormData]) VALUES (35512921264, 593, N'<fd u="=afa01113c0674a3fbdc8354c7aa538ab" b="0" v="1"><field i="1">12345</field><field i="2">E-mail Test</field><field i="3">1</field></fd>')





WITH [cte_test] AS
(
    SELECT TOP(10)
           [pd].[RecId],
           REPLACE(REPLACE(CAST(FormData.query('/fd/field[@i=1]') AS NVARCHAR(100)),'<field i="1">',''),'</field>','') AS [Order]
    FROM reports.[zz_PhoneData_Test] [pd]
)

SELECT  [cte].[RecId],
        [cte].[Order],
        CASE WHEN PATINDEX('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]', [Order] ) = 1 THEN 'Valid'
             WHEN PATINDEX('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', [Order] ) = 1 THEN 'Valid'
             WHEN PATINDEX('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]', [Order] ) = 1 THEN 'Valid'
             ELSE 'Invalid' END AS [Validation]
FROM [cte_test] [cte]
GO

With the above it should be enough to get you an example of where I am up to, ignore the PATINDEX as that can be left out of testing, I want to be able to pull the values for each of the 3 answers in the "XML" without using the 'query' option but via the 'nodes' method, although I'm not to sure on how to do this? Any help or guidance is appreciated.

What is your question again?

Salman Arshad
– Salman Arshad

2019-07-04 13:33:19 +00:00
Commented Jul 4, 2019 at 13:33 — Salman Arshad
– Salman Arshad, Commented Jul 4, 2019 at 13:33

Salman Arshad · Accepted Answer · 2019-07-04 14:16:48Z

1

You can use the nodes method like so:

SELECT t.RecID
     , t.TasksetID
     , n.field.value('@i', 'INT') AS attr
     , n.field.value('.', 'NVARCHAR(100)') AS text
FROM PhoneData AS t
CROSS APPLY t.FormData.nodes('/fd/field') AS n(field)

edited Jul 4, 2019 at 14:16

answered Jul 4, 2019 at 13:41

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Benzz Over a year ago

Thank you, this is what I am looking for although it comes out in several lines for the one ID, I assume I need to PIVOT to get this one line per RecId?

Salman Arshad Over a year ago

If there are exactly three answers you can ditch the cross apply and use

FormData.value('(/fd/field)[1]', 'VARCHAR'), FormData.value('(/fd/field)[2]', 'VARCHAR'), FormData.value('(/fd/field)[3]', 'VARCHAR')

Benzz Over a year ago

I appreciate the responses, the example I gave was more for simplicity, these answers could range from around 15-200 set questions depending which form a user submits. If it was only small range I would write each one like you have mentioned, although I think for the bigger ones it would be a pain but if that's the method that has to be used to gain the most efficient/best result then I'll do that.

Salman Arshad Over a year ago

Yes, PIVOT would be more appropriate if the number of column is not fixed.

Collectives™ on Stack Overflow

SQL - XML Parsing from a non-standard XML format

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related