Query with CROSS APPLY, trying to get a field based on an attribute value

Question

I try to get all the fields name from a form stored as XML in SQL Server, based on the value of an attribute (Culture). Actually, I can get the text value, but they are concatenated into the FieldName column.

XML structure looks like this:

<Form>
  <Field>
    <Field Name="email" Type="text" ColumnSize="6">
      <Localizations>
        <Localization Culture="fr">
          <Text>Adresse couriel</Text>
          <Placeholder>Entrez votre adresse courriel</Placeholder>
        </Localization>
        <Localization Culture="fr">
          <Text>Email</Text>
          <Placeholder>Enter your email</Placeholder>
        </Localization>
      </Localizations>
    </Field>
  </Fields>
</Form>

SQL Query:

DECLARE @language VARCHAR(2)
SET @language = 'en'
SELECT 
    c.query('data(@Name)') AS FieldUniqueName, 
    c.query('data(./Localizations/Localization/Text)') AS FieldName, 
    c.query('data(@Type)') AS FieldType
FROM dbo.Form Form CROSS APPLY FormContent.nodes('/Form/Fields/Field') x(c)
WHERE Id = @formId
AND FormContent.exist('/Form/Fields/Field/Localizations/Localization[@Culture=sql:variable("@language")]') = 1

Result look like this :

FieldUniqueName  |  FieldName             | FieldType
name             |  Nom Name              | text
email            |  Adresse couriel Email | text
phone            |  Téléphone Phone Number| text

Both languages are in the FieldName column!

I have tried also with :

...
c.value('data(./Localizations/Localization/Text)[1]', 'VARCHAR(100)') AS FieldName,
...

But I need to choose between [1] or [2] to get the right language...

score 2 · Answer 1 · answered Apr 25 '18 at 20:34

Besides the fact, that there were some errors in your provided XML (structure and "fr" for both), you can try this:

DECLARE @tbl TABLE(ID INT IDENTITY, FormContent XML);
INSERT INTO @tbl VALUES
(N'<Form>
  <Fields>
    <Field Name="email" Type="text" ColumnSize="6">
      <Localizations>
        <Localization Culture="fr">
          <Text>Adresse couriel</Text>
          <Placeholder>Entrez votre adresse courriel</Placeholder>
        </Localization>
        <Localization Culture="en">
          <Text>Email</Text>
          <Placeholder>Enter your email</Placeholder>
        </Localization>
      </Localizations>
    </Field>
  </Fields>
</Form>');

The query will return the loc values depending on your variable.

DECLARE @language VARCHAR(2)
SET @language = 'en'
SELECT 
    fld.value(N'@Name',N'nvarchar(max)') AS FieldUniqueName, 
    fld.value(N'@Type',N'nvarchar(max)') AS FieldType, 
    loc.value(N'(Text/text())[1]','nvarchar(max)') AS LocalText, 
    loc.value(N'(Placeholder/text())[1]','nvarchar(max)') AS LocalPlaceholder
FROM @tbl Form 
CROSS APPLY FormContent.nodes(N'/Form/Fields/Field') AS A(fld)
CROSS APPLY A.fld.nodes(N'Localizations/Localization[@Culture=sql:variable("@language")]') B(loc)
WHERE Id = 1

I'll give it a try and let mark your answer if it fulfill my requirement. Thanks. — Jonathan Anctil, Apr 26 '18 at 15:20

score 0 · Accepted Answer · answered Apr 25 '18 at 20:32

0

Ok, I found a response to my problem! Instead of having a where clause, I change the XPath of my FieldName. Works like a charm:

DECLARE @language VARCHAR(2)
SET @language = 'en'
SELECT 
    c.query('data(@Name)') AS FieldUniqueName, 
    c.query('data(./Localizations/Localization[@Culture=sql:variable("@language")]/Text)') AS FieldName, 
    c.query('data(@Type)') AS FieldType
FROM dbo.Form Form CROSS APPLY FormContent.nodes('/Form/Fields/Field') x(c)
WHERE Id = @formId

answered Apr 25 '18 at 20:32

Jonathan Anctil

1,025
3
20
44

1

Your former WHERE clause will just check, if this condition is fullfilled - somewhere in the XML. Well, it is... But using `data()` is not the right thing here... Better pack the condition into a second `.nodes()` call as `XQuery predicate` – Shnugo Apr 25 '18 at 20:36

Query with CROSS APPLY, trying to get a field based on an attribute value

2 Answers2